SQL Code Help

• ashok.theagarajan

  SSC-Addicted

  Points: 421

  More actions

  June 20, 2017 at 10:47 pm

  #322189

  Hi everyone,

  Below is a table example (Table name: Dummy)

  Number     Value
  12345          xyz
  12345          abc
  12345          zcd

  I need an SQL query which will give the result set as
  12345  xyz, abc, zcd

  Thanks in advance

• DesNorton

  SSC-Insane

  Points: 24283

  More actions

  June 20, 2017 at 11:43 pm

  #1947495

  ashok.theagarajan - Tuesday, June 20, 2017 10:47 PM

  Hi everyone,

  Below is a table example (Table name: Dummy)

  Number     Value
  12345          xyz
  12345          abc
  12345          zcd

  I need an SQL query which will give the result set as
  12345  xyz, abc, zcd

  Thanks in advance

  
SELECT
  a.Number
, Value = STUFF(( SELECT ',' + CAST(b.Value AS VARCHAR)
       FROM Dummy b
       WHERE a.Number = b.Number
       ORDER BY b.Value
       FOR XML PATH(''), TYPE).value('(./text())[1]','VARCHAR(MAX)'
      ), 1, 1, '')
FROM Dummy a
GROUP BY a.Number;


  ---

  How to post data/code on a forum to get the best help.

• ashok.theagarajan

  SSC-Addicted

  Points: 421

  More actions

  June 21, 2017 at 12:55 am

  #1947497

  Thank you !!!!! Works like a charm

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  June 21, 2017 at 10:08 am

  #1947594

  Or:
  WITH Dummy (Number, [Value]) AS
(
   SELECT
        a
      , b
   FROM
      (
         VALUES
              (12345, 'xyz')
            , (12345, 'abc')
            , (12345, 'zcd')) t (a, b)
)
SELECT
     a.Number
   , [xyz]
   , [abc]
   , [zcd]
FROM Dummy
PIVOT
   (Max(Value) FOR [Value] IN ([xyz]
   , [abc]
   , [zcd])) a;
  

• ashok.theagarajan

  SSC-Addicted

  Points: 421

  More actions

  June 21, 2017 at 10:38 am

  #1947602

  Hey ! Thanks, But as you can see it provides the result set in columns instead of it being on a single column with comma separated values.
  Also, the earlier method works for as many rows as i have the data for.....