Columns into pipe delimited list

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

Transact-SQL (2005)

Columns into pipe delimited list

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2009-10-23 : 13:52:54

Hi I have a query that generates the following columns of data:

C1 C2 C3

May 2004 120
May 2005 220
May 2006 230
June 2004 100
June 2005 200
June 2006 300
July 2004 110
July 2005 210
July 2006 310

What I'd like to return to the caller is one row of data as follows:

C1 C2 C3
May|June|July 2004|2005|2006 120,220,230|100,200,300|110,210,310

That's pipe-delimited data in the first two columns and then the third column is pipe-delimited groups of comma-separated data.
I can do this with some looping in the caller but was wondering how to approach this in SQL.

Thank you.
LW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:04:22

[code]
;With CTE (C1,C2,C3)AS
(
SELECT C1,C2,
STUFF((SELECT ','+ C3 FROM yourtable WHERE C1 = t.C1 ORDER BY C3 FOR XML PATH('')),1,1,'') AS Val
FROM Yourtable t
)

SELECT STUFF((SELECT DISTINCT '|'+C1 FROM CTE FOR XML PATH('')),1,1,'') AS C1,
STUFF((SELECT DISTINCT '|'+C2 FROM CTE FOR XML PATH('')),1,1,'') AS C2,
STUFF((SELECT DISTINCT '|'+C3 FROM CTE FOR XML PATH('')),1,1,'') AS C3
[/code]

littlewing
Starting Member

33 Posts

Posted - 2009-10-23 : 14:33:40

Man, thats sweet works great. Can't say I completely understand it but thanks very much.

LW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:35:12

welcome

littlewing
Starting Member

33 Posts

Posted - 2009-10-27 : 18:54:26

Hi, I tried this against some "real" application data and I found that the ordering/relation gets lost in the STUFF((SELECT DISTINCT ...

here's some real data (primary key is the first column):

1 Brown 2004 87.00
1 Brown 2005 55.00
1 Brown 2006 86.00
1 Brown 2007 82.00
1 Brown 2008 95.00
2 Green 2004 2.10
2 Green 2005 2.20
2 Green 2006 2.50
2 Green 2007 2.60
2 Green 2008 2.90
3 White 2004 24.00
3 White 2005 13.00
3 White 2006 23.00
3 White 2007 32.00
3 White 2008 24.00
4 Purple 2004 56.00
4 Purple 2005 35.00
4 Purple 2006 49.00
4 Purple 2007 41.00
4 Purple 2008 41.00
10 Overall 2004 170.00
10 Overall 2005 105.00
10 Overall 2006 162.00
10 Overall 2007 157.00
10 Overall 2008 163.00

I'd like to derive and return:
Column 1 as Brown|Green|White|Purple|Overall
Column 2 as 2004|2005|2006|2007|2008
Column 3 as 87.00,55.00,86.00,82.00,95.00|2.10,2.20,2.50,2.60,2.90|24.00,13.00,23.00,32.00,24.00|56.00,35.00,49.00,41.00,41.00|170.00,105.00,162.00,157.00,163.00

Executing SELECT STUFF((SELECT DISTINCT '|'+C1 FROM CTE FOR XML PATH('')),1,1,'') AS C1, to reorders the data so Brown|Green|White|Purple|Overall is returned as Brown|Overall|White|Purple|Green for example.

Can the order (is that the right word? Cardinality?) of the data be maintained?

Thanks again
LW

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources