Pivot and Merge Columns

• dwilliscp

  SSCrazy Eights

  Points: 9270

  More actions

  August 15, 2013 at 2:03 pm

  #278782

  Is there any way to take the following:

  Part Whse

  11c ATL

  11c Day

  11c BHAM

  21E ATL

  21E CINCY

  23M DAY

  and merge the various WHSE into a single column.. like

  11c ATL, Day, BHAM

  21e ATL, CINCY

  23m DAY

  What I want to do.. is the above in a CTE then join for the main query on CTE.part = main.part.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 15, 2013 at 2:53 pm

  #1641928

  I'm not sure what you need to do in the end, but this code should get you started and the article can help you to understand what's going on.

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

  WITH Parts( Part, Whse) AS(

  SELECT *

  FROM (VALUES

  ('11c', 'ATL'),

  ('11c', 'Day'),

  ('11c', 'BHAM'),

  ('21E', 'ATL'),

  ('21E', 'CINCY'),

  ('23M', 'DAY'))x( Part, Whse)

  ),

  DistinctParts AS(

  SELECT DISTINCT Part

  FROM Parts)

  SELECT Part,

  CommaList = STUFF((

  SELECT ',' + Whse

  FROM Parts p

  WHERE p.Part = dp.Part

  FOR XML PATH('')),1,1,'')

  FROM DistinctParts dp

  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