Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

Creating a comma-separated list (SQL Spackle) Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 3:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
COTS... say no more. I feel your pain.

Wayne
Microsoft Certified Master: SQL Server 2008
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
Post #1377871
Posted Monday, October 29, 2012 8:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:42 AM
Points: 1,322, Visits: 793
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



Post #1378542
Posted Tuesday, October 30, 2012 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 4:47 AM
Points: 7, Visits: 19
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
Post #1378802
Posted Tuesday, October 30, 2012 8:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 36,950, Visits: 31,456
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378820
Posted Tuesday, October 30, 2012 8:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
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
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
Post #1378829
Posted Tuesday, October 30, 2012 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 4:47 AM
Points: 7, Visits: 19
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?
Post #1378905
Posted Tuesday, October 30, 2012 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 4:47 AM
Points: 7, Visits: 19
^^^ or indeed any sense at all?
Post #1378907
Posted Tuesday, October 30, 2012 12:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
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
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
Post #1378959
Posted Tuesday, October 30, 2012 7:05 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 4:36 PM
Points: 494, Visits: 1,122
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..
Post #1379119
Posted Thursday, November 8, 2012 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:08 PM
Points: 7,097, Visits: 12,599
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
Post #1382750
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse