Collapsing rows to one record - SQL Server Forums

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 2005 Forums

SSIS and Import/Export (2005)

Collapsing rows to one record

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-20 : 08:52:00

What's the SQL statement please... thanks.

Data -


ID    Col1    Col2
1     cat1
1     cat2
1     cat3
1             dog1
1             dog2
1             dog3

Desired result -


ID    Col1             Col2
1     cat1,cat2,cat3   dog1,dog2,dog3

Thanks a bunch!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 09:08:04

[code]SELECT t.ID,
LEFT(cl1.Col1List,LEN(cl1.Col1List)-1) AS Col1,
LEFT(cl2.Col2List,LEN(cl2.Col2List)-1) AS Col2
FROM (SELECT DISTINCT ID FROM Table) t
CROSS APPLY (SELECT Col1 + ','
FROM Table
WHERE ID=t.ID
AND Col1 >''
FOR XML PATH(''))cl1(Col1List)
CROSS APPLY (SELECT Col2 + ','
FROM Table
WHERE ID=t.ID
AND Col2 >''
FOR XML PATH(''))cl2(Col2List)[/code]

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-20 : 09:16:49

Wow, thanks visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 09:21:02

Welcome

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-20 : 09:24:30

Sorry, do you have SQL 2000 equivalent? Thanks.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-20 : 10:35:37

No worry, I'll find it. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:50:00

quote:
Originally posted by rgombina

No worry, I'll find it. Thanks!

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-20 : 11:06:42

Thanks! This did the trick -


   SELECT CategoryId,
           MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )

      FROM ( SELECT p1.CategoryId, p1.ProductName,
                    ( SELECT COUNT(*) 
                        FROM Northwind.dbo.Products p2
                        WHERE p2.CategoryId = p1.CategoryId
                        AND p2.ProductName <= p1.ProductName )
             FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq )
     GROUP BY CategoryId ;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:30:04

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources