SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Concatenate data in the same table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/28/2012 :  08:26:51  Show Profile  Reply with Quote
I have a relatively simple table that i need to consolidate some of the data in. The data looks like:


Offenseno	Uniquekey	Narrative	OffenseReportUniqueFKey
DCSO99OFF181781	3QXH4V5lP9U88ZCrAJysdl	Additional Abuse types data in this case are:  Abusive Language	3tNHjG7Nz1bOieBCXBVyjv
DCSO99OFF212830	1X0nu5A|5CxQ4{5CLhe7Ra	Additional Abuse types data in this case are:  Abusive Language	1VttHK2kP119spENWOY2XZ
DCSO99OFF214928	2{KuTcQgj3rh4u|cO|pWUq	Additional Abuse types data in this case are:  Abusive Language	0HhCEmJ1LCnxcdCKonxILl
DCSO99OFF224676	0|iTLY0efBWv4sHH1U0qkF	Additional Abuse types data in this case are:  Abusive Language	1T9jMQCenBGuoXLnuJJ6Z{
DCSO99OFF233822	0Q5bfi6m92xeW7iotjIS73	Additional Abuse types data in this case are:  Sexual Abuse	1ucqh6UWj8PRAqygX0WibT
DCSO99OFF236865	3sBAouGOb73wgNLb3REGtD	Additional Abuse types data in this case are:  Abusive Language	2Y5qarZAvFgxQxjd0kuFHc
DCSO99OFF240914	05Cnr|TNDC19pzsveqmtkG	Additional Abuse types data in this case are:  Abusive Language	17o5BbIg9FkP2{v3g2G991
DCSO99OFF241112	3pwtP872nFNxq|KBXhEacH	Additional Abuse types data in this case are:  Threats	3|H4a3mk164xpzALMqLe{4
DCSO99OFF241112	0rqU5EBtX2LwHClda3DVJb	Additional Abuse types data in this case are:  Abusive Language	3|H4a3mk164xpzALMqLe{4
DCSO99OFF241112	15Rog{4x11Ih4rmm2hlW5M	Additional Abuse types data in this case are:  Other	3|H4a3mk164xpzALMqLe{4
DCSO99OFF244819	3T23TX4sLFQPZuggIsIhaa	Additional Abuse types data in this case are:  Abusive Language	3X4LeR00T2hQyTC|Rtu5H4



Where the Offenseno is the same in more than one row, i only need ONE row to exist. The narrative for that one row needs to contain the abuse types listed in the narrative for the other rows that i will get rid of. So you see with offenseno DCSO99OFF241112 there are three abusetypes listed across three different rows: "Threats, Abusive Language, and Other". In that case, what i would do is keep the top entry, "Threats", and add to it ONLY the characters after the ":" so the one row would read: "Additional Abuse types data in this case are: Threats, Abusive Language, Other".

I know there is either a Charindex or a patindex that needs to happen, but that's as much as i can guess about how to do this at this point.

Thanks in advance for your help.

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 09/28/2012 :  08:39:15  Show Profile  Reply with Quote
Instead of concatenating in the table, can you normalize the data and create another table?








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/28/2012 :  08:41:12  Show Profile  Reply with Quote
yes, that would work just as well, i suppose. But "how" is still the question.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  08:45:20  Show Profile  Reply with Quote
You can extract just the extra offenses using stuff function:
SELECT 
	OffenseNo,
	STUFF(Narrative,1,CHARINDEX(':',Narrative),'') as Offense
FROM
	YourTable
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  08:50:55  Show Profile  Reply with Quote
If you need to get the concatenated offense list, you can query it like this:
SELECT
	a.Offenseno,
	'Additional Abuse types data in this case are:  '+
		STUFF(b.Narratives,1,2,'') AS Narratives
FROM
	(SELECT DISTINCT Offenseno FROM YourTable ) AS a
	CROSS APPLY
	(
		SELECT
			', ' AS [text()],
			STUFF(b.Narrative,1,CHARINDEX(':',b.Narrative),'') AS [text()]
		FROM
			YourTable b
		WHERE 
			b.Offenseno = a.Offenseno
		FOR XML PATH('')
	) b(Narratives);
If there are some horrible offenses with funny characters such as <, &, > etc., you we may need to do some more changes, but otherwise, this should work.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/28/2012 :  09:04:09  Show Profile  Reply with Quote
;With cte
as (SELECT t.Offenseno, STUFF((SELECT ',' + STUFF(Narrative,1,CHARINDEX(':',Narrative),'') FROM @Tab s WHERE s.Offenseno = t.Offenseno FOR XML PATH('')),1,1,'') AS CSV
FROM @Tab AS t GROUP BY t.Offenseno)
SELECT c.Offenseno, uniquekey, 'Additional Abuse types data in this case are:' + c.csv as narrative, OffenseReportUniqueFKey
FROM cte c JOIN
(SELECT *, row_number() over(PARTITION by offenseno order by narrative) as rn FROM @tab) t ON t.Offenseno = c.Offenseno and rn = 1


--
Chandu
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/28/2012 :  09:04:59  Show Profile  Reply with Quote
Sunita,

The first example just chops off everything before the ":" in each row and does not concatenate any abuse types from rows with the same offenseno. The second example produces a smaller resultset than the original table contains but it contains rows with only one abuse type as well as a couple of rows with a concatenated set of abuse types in one row.
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/28/2012 :  09:10:50  Show Profile  Reply with Quote
and the data in all Narrative fields contains no characters other than what you see in my example, so no contingencies are needed.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  09:18:47  Show Profile  Reply with Quote
quote:
Originally posted by WJHamel

Sunita,

The first example just chops off everything before the ":" in each row and does not concatenate any abuse types from rows with the same offenseno. The second example produces a smaller resultset than the original table contains but it contains rows with only one abuse type as well as a couple of rows with a concatenated set of abuse types in one row.

Meh! I must be confused because it is Friday. I thought that is what you wanted. Ignore if that is not it.
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/28/2012 :  09:20:39  Show Profile  Reply with Quote
Sunita. Correction. Your second example provides the correct resultset and does essentially what Bandi's example does. Now i just need to add the correct code to make it select that resutlset into a new table. I'll check back if i can't figure that part out myself. Thanks all.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/28/2012 :  09:21:22  Show Profile  Reply with Quote
DECLARE @tab TABLE(Offenseno	varchar(100), Uniquekey	varchar(100), Narrative	varchar(300), OffenseReportUniqueFKey varchar(100))
INSERT INTO @tab 
VALUES('DCSO99OFF181781',	'3QXH4V5lP9U88ZCrAJysdl',	'Additional Abuse types data in this case are:  Abusive Language',	'3tNHjG7Nz1bOieBCXBVyjv'),
('DCSO99OFF212830',	'1X0nu5A|5CxQ4{5CLhe7Ra',	'Additional Abuse types data in this case are:  Abusive Language',	'1VttHK2kP119spENWOY2XZ'),
('DCSO99OFF214928',	'2{KuTcQgj3rh4u|cO|pWUq',	'Additional Abuse types data in this case are:  Abusive Language',	'0HhCEmJ1LCnxcdCKonxILl'),
('DCSO99OFF224676',	'0|iTLY0efBWv4sHH1U0qkF',	'Additional Abuse types data in this case are:  Abusive Language',	'1T9jMQCenBGuoXLnuJJ6Z{'),
('DCSO99OFF233822',	'0Q5bfi6m92xeW7iotjIS73',	'Additional Abuse types data in this case are:  Sexual Abuse',	'1ucqh6UWj8PRAqygX0WibT'),
('DCSO99OFF236865',	'3sBAouGOb73wgNLb3REGtD',	'Additional Abuse types data in this case are:  Abusive Language',	'2Y5qarZAvFgxQxjd0kuFHc'),
('DCSO99OFF240914',	'05Cnr|TNDC19pzsveqmtkG',	'Additional Abuse types data in this case are:  Abusive Language',	'17o5BbIg9FkP2{v3g2G991'),
('DCSO99OFF241112',	'3pwtP872nFNxq|KBXhEacH',	'Additional Abuse types data in this case are:  Threats',	'3|H4a3mk164xpzALMqLe{4'),
('DCSO99OFF241112',	'0rqU5EBtX2LwHClda3DVJb',	'Additional Abuse types data in this case are:  Abusive Language',	'3|H4a3mk164xpzALMqLe{4'),
('DCSO99OFF241112',	'15Rog{4x11Ih4rmm2hlW5M',	'Additional Abuse types data in this case are:  Other',	'3|H4a3mk164xpzALMqLe{4'),
('DCSO99OFF244819',	'3T23TX4sLFQPZuggIsIhaa',	'Additional Abuse types data in this case are:  Abusive Language',	'3X4LeR00T2hQyTC|Rtu5H4')

;With cte 
as (SELECT t.Offenseno, STUFF((SELECT ',' + STUFF(Narrative,1,CHARINDEX(':',Narrative),'') FROM @Tab s WHERE s.Offenseno = t.Offenseno FOR XML PATH('')),1,1,'') AS CSV
	FROM @Tab AS t GROUP BY t.Offenseno)
SELECT c.Offenseno, uniquekey, 'Additional Abuse types data in this case are:' + c.csv as narrative, OffenseReportUniqueFKey
FROM cte c 
JOIN 
(SELECT *, row_number() over(PARTITION by offenseno order by narrative) as rn FROM @tab) t 
ON t.Offenseno = c.Offenseno and rn = 1
 


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000