SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a comma-separated list (SQL Spackle)


Creating a comma-separated list (SQL Spackle)

Author
Message
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21358 Visits: 10652
COTS... say no more. I feel your pain.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

cs_troyk
cs_troyk
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2126 Visits: 973
Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.

-TroyK



chris.westgate
chris.westgate
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 43
Great solution Wayne, thank you...

But for me, here's a tiny spanner in the works.

The column that I need to produce a delimited list from is an int column - and I need the list ordered by integer. However, trying to concatenate ',' with an int of course results in a conversion error trying to convert ',' to an int... BUT, if I cast my int value as a varchar, of course, it then produces a list that runs 1,10,11, 12, 2, 20... etc...

Any ideas please?

Cheers
Chris
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212223 Visits: 41977
chris.westgate (10/30/2012)
Great solution Wayne, thank you...

But for me, here's a tiny spanner in the works.

The column that I need to produce a delimited list from is an int column - and I need the list ordered by integer. However, trying to concatenate ',' with an int of course results in a conversion error trying to convert ',' to an int... BUT, if I cast my int value as a varchar, of course, it then produces a list that runs 1,10,11, 12, 2, 20... etc...

Any ideas please?

Cheers
Chris


You've basically just answered you own question. The only way to concatenate INTs will be to convert them. Just add an ORDER BY to you XML based SELECT and Bob's your uncle.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21358 Visits: 10652
chris.westgate (10/30/2012)
Great solution Wayne, thank you...

But for me, here's a tiny spanner in the works.

The column that I need to produce a delimited list from is an int column - and I need the list ordered by integer. However, trying to concatenate ',' with an int of course results in a conversion error trying to convert ',' to an int... BUT, if I cast my int value as a varchar, of course, it then produces a list that runs 1,10,11, 12, 2, 20... etc...

Any ideas please?

Cheers
Chris


Hi Chris,

In the article, I have an "ORDER BY Value" to control the ordering (just prior to the FOR XML). Just use your integer column there. As you (and Jeff) point out, just convert your int to a varchar to create the delimited list.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

chris.westgate
chris.westgate
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 43
Sorry. Left out a couple of critical bits of information. I'm selecting two columns and ordering by one of them - but trying to get the CS string for the second column, against the first column... both columns are integers, and the second column has repeated values but I need the distinct list... and of course if distinct is used, the exact expression must appear in the order by list.

However, because I've got to cast it as a varchar to concatenate the comma, I can only order by the cast as varchar - which doesn't give the correct integer sorting...

...does that make more sense now?
chris.westgate
chris.westgate
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 43
^^^ or indeed any sense at all? ;-)
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21358 Visits: 10652
So, what you are looking for is:

Col1, comma-separated list of Column 2 (in order)

Col1 should only be there once.
Col2 is an integer, and should be sorted by it's value.

Is this correct?

So, this is what you're looking for:
DECLARE @table TABLE (
Col1 INT,
Col2 INT);

INSERT INTO @table (Col1, Col2)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 5 UNION ALL
SELECT 3, 10 UNION ALL
SELECT 3, 15;

WITH cte AS
(
SELECT DISTINCT Col1
FROM @table
)
SELECT Col1,
CS = STUFF((SELECT ',' + CONVERT(VARCHAR(10), Col2)
FROM @table t1
WHERE t1.col1 = cte.Col1
ORDER BY Col2
FOR XML PATH(''), TYPE).value('.','varchar(max)'),
1, 1, '')
FROM cte
ORDER BY Col1;



Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Possinator
Possinator
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 1123
Just a note of caution - if you plan to string together integers with a comma delimiter and subsquently dump the data into Excel, Excel will try to do funny things with the string. I just ran into this today. I changed the delimiter to a pipe (|) and all is well.

Looking for a Deadlock Victim Support Group..
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38986 Visits: 14411
cs_troyk (10/29/2012)
Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.

-TroyK

MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. Notice that the query in the article is not an aggregate, i.e. no GROUP BY clause.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search