Help Select Query

Question

Post reply

Help Select Query

kausmail

SSC Enthusiast

Points: 151
More actions
February 14, 2014 at 1:28 pm

#304148

I get following results from table1:

Sys TeamID

----- ----------

100 Team-1

100 Team-2

100 Team-3

I want the results in following way:

Sys TeamID

---- ----------

100 Team-1, Team-2, Team-3

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

probably using FOR XML to concatenate the values is what you want.

here's an example:



declare @skills table (sys int,  TeamId varchar(20))

insert into @skills

SELECT '100','Team-1' UNION ALL

SELECT '100','Team-2' UNION ALL

SELECT '100','Team-3' UNION ALL

SELECT '101','Team-4' UNION ALL

SELECT '101','Team-5' UNION ALL

SELECT '101','Team-6' UNION ALL

SELECT '101','Team-7'

---

select * from @skills s1

---  Concatenated Format

set statistics time on;

SELECT sys,stuff((    SELECT ',' + TeamId

                               FROM @skills s2

                             WHERE s2.sys= s1.sys   --- must match GROUP BY below

                             ORDER BY TeamId

                               FOR XML PATH('')

                            ),1,1,'')  as [Skills]

FROM @skills s1

GROUP BY s1.sys --- without GROUP BY multiple rows are returned

ORDER BY s1.sys

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!