transposing data using pivot and unpivot

• koolme_85

  Ten Centuries

  Points: 1153

  More actions

  June 5, 2015 at 7:25 am

  #318050

  My table structure is like

  col1 col2 col3 col4 col5 col6

  abc. def. 3fg. 59j. 567. 596040

  abc. def. 3fg. 59j. 567. 596042

  abc. def. 3fg. 59j. 567. 596043

  abc. def. 3fg. 59j. 567. 596044

  edf. ijk. rkl. 1fh. 567. 596045

  edf. ijk. rkl. 1fh. 567. 596046

  edf. ijk. rkl. 1fh. 567. 596047

  edf. ijk. rkl. 1fh. 567. 596048

  edf. ijk. rkl. 1fh. 567. 596049

  and I am trying to get the above data , gel them ino col 6 by comma separated

  col1 col2 col3 col4 col5 col6

  abc def 3fg 59j 567 596040,567 596042,567 596043,567 596044

  edf ijk rkl 1fh 567 596045,596046,596047,596048,596049

  can I get an example query for this , fairly new to SQL any help would be greatly appreciated

  thanks

• spaghettidba

  SSC Guru

  Points: 105712

  More actions

  June 5, 2015 at 8:31 am

  #1803318

  DECLARE @sampleData TABLE(

  col1 VARCHAR(4)

  , col2 VARCHAR(4)

  , col3 VARCHAR(4)

  , col4 VARCHAR(4)

  , col5 VARCHAR(4)

  , col6 VARCHAR(max)

  );

  INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596040');

  INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596042');

  INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596043');

  INSERT INTO @sampleData VALUES ('abc.','def.','3fg.','59j.','567.','596044');

  INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596045');

  INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596046');

  INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596047');

  INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596048');

  INSERT INTO @sampleData VALUES ('edf.','ijk.','rkl.','1fh.','567.','596049');

  SELECT DISTINCT col1, col2, col3, col4, col5, col6 = STUFF((

  SELECT ',' + col5 + ' ' + col6 AS [text()]

  FROM @sampleData

  WHERE col1 = s.col1

  AND col2 = s.col2

  AND col3 = s.col3

  AND col4 = s.col4

  FOR XML PATH(''), TYPE

  ).value('.','varchar(max)'),1,1,'')

  FROM @sampleData AS s

  -- Gianluca Sartori