August 12, 2015 at 7:16 am
Hi,
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
SELECT 89889, 'Posted'
UNION ALL
SELECT 89889, 'Complete'
UNION ALL
SELECT 89889, 'Delivered'
UNION ALL
SELECT 89000, 'Delivered'
UNION ALL
SELECT 89001, 'In Progress'
SELECT * FROM #Status
CREATE TABLE #Orders
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
NewStatusCombined VARCHAR(100)
)
INSERT INTO #Orders ( OrderID, NewStatusCombined )
SELECT 89889,NULL
UNION ALL
SELECT 89000,NULL
UNION ALL
SELECT 89001,NULL
SELECT * FROM #Orders
--DESIRED RESULT
SELECT * FROM #Orders
IDOrderIDNewStatusCombined
189889Posted,Complete,Delivered
289000Delivered
389001In Progress
DROP TABLE #Status
DROP TABLE #Orders
Thanks,
PSB
August 12, 2015 at 7:40 am
Why are you doing that? It will only make your querying more painful. You shouldn't store comma-delimited values in the db.
August 12, 2015 at 7:48 am
It will be used in a temp table for a report procedure . Not stored in the db.
August 12, 2015 at 7:53 am
Then just follow the process described in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
WITH cteOrders AS(
SELECT DISTINCT OrderID
FROM #Status
)
SELECT OrderID,
STUFF((SELECT ',' + Status
FROM #Status s
WHERE o.OrderID = s.OrderID
ORDER BY ID
FOR XML PATH('')), 1, 1, '')
FROM cteOrders o;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply