December 25, 2015 at 11:00 am
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
December 25, 2015 at 1:16 pm
Wayne Sheffield has an excellent post that explains this very well at http://www.sqlservercentral.com/articles/comma+separated+list/71700/.
December 25, 2015 at 11:22 pm
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
December 26, 2015 at 8:04 am
Refer below post to understand how to display row values as a single comma separated values
December 26, 2015 at 10:03 am
sandeepmittal11 (12/26/2015)
Refer below post to understand how to 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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply