Help Select Query

• kausmail

  SSC Enthusiast

  Points: 181

  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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 14, 2014 at 1:35 pm

  #1689208

  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!