concatenation from multiple rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

concatenation from multiple rows

Author

Topic

telynch25
Starting Member

9 Posts

Posted - 2010-05-25 : 18:02:28

Hello,
I'm having some trouble writing this code and need some assistance with it...and understanding it.

I have a table that defines values that need to be grouped as to not create multiple entries in another table, but the data is dynamically being populated and I'm having a tough time getting it to work...


Table layout
ChngId  Server   ChngDetailId   ChngStart   ChngEnd     AssgTo  Desc
1       aaa      2              2010-06-01  2010-06-01  xxx     Do This
1       aaa      2              2010-06-01  2010-06-01  yyy     Do This too.
1       aab      2              2010-06-01  2010-06-01  xxx     Do this.

What I'm trying to do is that it pulls the 1st & 3rd records as one since they are the DISTINCT ChngId, ChngStart, ChngEnd, AssgTo, but then it needs to create a new output in a seperate field (NewDesc) joining the Desc for both as 1 field, instead of the 2 seperate ones currently.

So the above would come out looking like this.


OUTPUT
ChngId  ChngDetailId   ChngStart   ChngEnd     AssgTo  NewDesc
1       2              2010-06-01  2010-06-01  xxx     aaa: Do This aab: Do This.
1       2              2010-06-01  2010-06-01  yyy     aaa: Do This too.

A select should do since this will be used as a INSERT INTO for another table.

I'm wracking my brain here...Thanks in advance!!!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-26 : 00:21:32

Try this:

DECLARE @Sample TABLE (ChngId INT,
Server VARCHAR(100),
ChngDetailId INT,
ChngStart DATETIME,
ChngEnd DATETIME,
AssgTo VARCHAR(100),
Descr VARCHAR(255)
)

iNSERT INTO @Sample
SELECT 1 ,'aaa' , 2 , '2010-06-01' ,'2010-06-01' ,'xxx' ,'Do This' UNION
SELECT 1 ,'aaa', 2 ,'2010-06-01' ,'2010-06-01' ,'yyy' ,'Do This too.' UNION
SELECT 1 ,'aab' ,2 ,'2010-06-01' ,'2010-06-01' ,'xxx' ,'Do this.'

SELECT * FROM @Sample

SELECT DISTINCT s1.ChngID,s1.ChngDetailId,s1.ChngStart, s1.ChngEnd, s1.AssgTo,
STUFF((SELECT DISTINCT TOP 100 PERCENT [Server] + ': ' + s2.descr + ' ' FROM @Sample AS s2 WHERE s2.ChngId = s1.ChngId
and s2.AssgTo = s1.AssgTo
ORDER BY [Server] +': ' + s2.Descr + ' ' FOR XML PATH('')), 1, 0, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ChngId

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

telynch25
Starting Member

9 Posts

Posted - 2010-05-26 : 10:51:45

Bohra, thanks a ton! I need to read up on the STUFF command...I've seen it used in some other scripts written by a few friends of mine, but have never learned it.

Thanks again! It worked perfectly!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-26 : 11:02:38

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources