Comma separated values

• PSB

  SSChampion

  Points: 13195

  More actions

  December 25, 2015 at 11:00 am

  #310442

  Hi,

  I need a comma separated list of values for GeoMarkets when it falls into a certain criteria

  CREATE TABLE #DeploymentPlan

  (

  WorkFlow VARCHAR(100),

  Deliverable VARCHAR(100),

  [Deliverable Phase] VARCHAR(500),

  [Segment] VARCHAR(100),

  [GeoMarket] VARCHAR(100),

  [TaskActualFinishDate] DATETIME

  )

  INSERT INTO #DeploymentPlan ( WorkFlow,Deliverable,[Deliverable Phase],[Segment],[GeoMarket],[TaskActualFinishDate])

  SELECT 'PO','Activity to Demand','ITEP1','AL','APG','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ABC','2015-09-11 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ABC','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ABC','2015-12-11 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ANG','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL','APG','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','_Central','_Central','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','CPL','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','D&M','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','MI','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','WL','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','WS','_Segment','2015-11-23 17:00:00.000'

  SELECT * FROM #DeploymentPlan

  --DESIRED result

  --When WorkFlow,Deliverable,Deliv Phase,Segment and Dateis same then GeoMarket should be coma separated value for that row

  SELECT 'PO','Activity to Demand','ITEP1','CPL','ANG,APG','2015-10-23 17:00:00.000'

  --Other rows remain same

  DROP TABLE #DeploymentPlan

  Thanks,

  PSB

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  December 25, 2015 at 1:16 pm

  #1848064

  Wayne Sheffield has an excellent post that explains this very well at http://www.sqlservercentral.com/articles/comma+separated+list/71700/.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  December 25, 2015 at 11:22 pm

  #1848097

  Quick solution

  😎CREATE TABLE #DeploymentPlan

  (

  WorkFlow VARCHAR(100)

  ,Deliverable VARCHAR(100)

  ,[Deliverable Phase] VARCHAR(500)

  ,[Segment] VARCHAR(100)

  ,[GeoMarket] VARCHAR(100)

  ,[TaskActualFinishDate] DATETIME

  )

  INSERT INTO #DeploymentPlan ( WorkFlow,Deliverable,[Deliverable Phase],[Segment],[GeoMarket],[TaskActualFinishDate])

  SELECT 'PO','Activity to Demand','ITEP1','AL','APG','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ABC','2015-09-11 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ABC','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ABC','2015-12-11 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL', 'ANG','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'PO','Activity to Demand','ITEP1','CPL','APG','2015-10-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','_Central','_Central','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','CPL','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','D&M','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','MI','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','WL','_Segment','2015-11-23 17:00:00.000' UNION ALL

  SELECT 'DPS','Activity to Demand','ITEP1','WS','_Segment','2015-11-23 17:00:00.000'

  ;WITH BASE_DATA AS

  (

  SELECT

  DENSE_RANK() OVER

  (

  ORDER BY DP.WorkFlow

  ,DP.Deliverable

  ,DP.[Deliverable Phase]

  ,DP.Segment

  ,DP.TaskActualFinishDate

  ) AS INST_ID

  ,ROW_NUMBER() OVER

  (

  PARTITION BY DP.WorkFlow

  ,DP.Deliverable

  ,DP.[Deliverable Phase]

  ,DP.Segment

  ,DP.TaskActualFinishDate

  ORDER BY (SELECT NULL)

  ) INST_RID

  ,DP.WorkFlow

  ,DP.Deliverable

  ,DP.[Deliverable Phase]

  ,DP.Segment

  ,DP.TaskActualFinishDate

  ,DP.GeoMarket

  FROM #DeploymentPlan DP

  )

  SELECT

  BD.WorkFlow

  ,BD.Deliverable

  ,BD.[Deliverable Phase]

  ,BD.Segment

  ,BD.TaskActualFinishDate

  ,(STUFF(

  (SELECT

  CHAR(44) + SBD.GeoMarket

  FROM BASE_DATA SBD

  WHERE BD.INST_ID = SBD.INST_ID

  FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(512)'),1,1,'')

  ) AS GeoMarket

  FROM BASE_DATA BD

  WHERE BD.INST_RID = 1

  DROP TABLE #DeploymentPlan

  Results

  WorkFlow Deliverable Deliverable Phase Segment TaskActualFinishDate GeoMarket

  --------- ------------------ ------------------ --------- ----------------------- -------------

  DPS Activity to Demand ITEP1 _Central 2015-11-23 17:00:00.000 _Central

  DPS Activity to Demand ITEP1 CPL 2015-11-23 17:00:00.000 _Segment

  DPS Activity to Demand ITEP1 D&M 2015-11-23 17:00:00.000 _Segment

  DPS Activity to Demand ITEP1 MI 2015-11-23 17:00:00.000 _Segment

  DPS Activity to Demand ITEP1 WL 2015-11-23 17:00:00.000 _Segment

  DPS Activity to Demand ITEP1 WS 2015-11-23 17:00:00.000 _Segment

  PO Activity to Demand ITEP1 AL 2015-10-23 17:00:00.000 APG

  PO Activity to Demand ITEP1 CPL 2015-09-11 17:00:00.000 ABC

  PO Activity to Demand ITEP1 CPL 2015-10-23 17:00:00.000 ABC,ANG,APG

  PO Activity to Demand ITEP1 CPL 2015-12-11 17:00:00.000 ABC

• sandeepmittal11

  SSC Veteran

  Points: 228

  More actions

  December 26, 2015 at 8:04 am

  #1848110

  Refer below post to understand how to display row values as a single comma separated values

  Display row values as a single comma separated values

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 26, 2015 at 10:03 am

  #1848116

  sandeepmittal11 (12/26/2015)

  ---

  Refer below post to understand how to display row values as a single comma separated values

  Display row values as a single comma separated values

  Again, not a bad article but it's missing a lot of meat. Help your readers by explaining things like why it works, why it's necessary to have the GROUP BY and STUFF and why it's essential to have the correlation in the WHERE clause. Then go back and explain how to prevent the problems in the output when you have an entry like this...

  SELECT '100SON','Sony', 'Combination DVD & VHS Player <refurbished>' UNION ALL

  ...as well as how to control the data-type of the output, especially when you exceed NVARCHAR(4000) or VARCHAR(8000).

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)