Pivot Data for multiple Strings

• myjobsinus

  SSC Eights!

  Points: 964

  More actions

  January 6, 2016 at 8:50 am

  #323847

  Hi All,

  I have a source table as following

  CREATE TABLE #Source (ID1 int, ID2 int, ID3 int, String varchar(50))

  INSERT INTO #Source VALUES (2234,4461,2349,'876: TEST REASON 1')

  INSERT INTO #Source VALUES (2234,4461,2349,'225: TEST REASON 2')

  INSERT INTO #Source VALUES (2234,4461,2349,'198: TEST REASON 3')

  INSERT INTO #Source VALUES (1145,1872,6713,'876: TEST REASON 1')

  INSERT INTO #Source VALUES (1145,1872,6713,'198: TEST REASON 3')

  SELECT * FROM #Source

  --And i need a t-sql query to out put results as following

  CREATE TABLE #Results (ID1 int, ID2 int, ID3 int, String_Combined varchar(500))

  INSERT INTO #Results VALUES (2234,4461,2349,'876: TEST REASON 1 , 225: TEST REASON 2, 198: TEST REASON 3')

  INSERT INTO #Results VALUES (1145,1872,6713,'876: TEST REASON 1, 198: TEST REASON 3')

  SELECT * FROM #Results

  -- I have tried pivot, which works ok with numbers but is not able to concatenate strings. Any help is appreciated.

  Many Thanks.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 6, 2016 at 9:20 am

  #1849908

  Here's a nice short article about concatenating strings:

  http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  Here's an example based on what you posted:

  WITH CTE AS(

  SELECT DISTINCT ID1, ID2, ID3

  FROM #Source

  )

  SELECT ID1, ID2, ID3,

  STUFF( (SELECT ', ' + String

  FROM #Source s

  WHERE s.ID1 = c.ID1

  AND s.ID2 = c.ID2

  AND s.ID3 = c.ID3

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'), 1, 2, '') AS String_Combined

  FROM CTE c;

  Feel free to ask any questions you might have.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182884

  More actions

  January 10, 2016 at 4:09 am

  #1850801

  Luis Cazares (1/6/2016)

  ---

  Here's a nice short article about concatenating strings:

  http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  Here's an example based on what you posted:

  WITH CTE AS(

  SELECT DISTINCT ID1, ID2, ID3

  FROM #Source

  )

  SELECT ID1, ID2, ID3,

  STUFF( (SELECT ', ' + String

  FROM #Source s

  WHERE s.ID1 = c.ID1

  AND s.ID2 = c.ID2

  AND s.ID3 = c.ID3

  FOR XML PATH(''),TYPE).value('.', 'varchar(max)'), 1, 2, '') AS String_Combined

  FROM CTE c;

  Feel free to ask any questions you might have.

  Quick suggestion, change the parameters in the xml value clause to use the text() method, much faster that way.

  😎

  WITH CTE AS(

  SELECT DISTINCT ID1, ID2, ID3

  FROM #Source

  )

  SELECT ID1, ID2, ID3,

  STUFF( (SELECT ', ' + String

  FROM #Source s

  WHERE s.ID1 = c.ID1

  AND s.ID2 = c.ID2

  AND s.ID3 = c.ID3

  FOR XML PATH(''),TYPE).value('(./text())[1]', 'varchar(max)'), 1, 2, '') AS String_Combined

  FROM CTE c;