T-SQL CONCATE ROW values from #Temporary Table

Question

I'm currently working on a SQL query in T-SQL on SQL Server 2012 with the objective to concate or merge several tables into 1 table and concate several rows. So far it's working out fine. I use STUFF and FOR XML PATH to concate several rows into 1.

Unfortunately, when I want to merge my 2 temporary tables into 1 final result table, the concatinated (merged) rows disappear again.

My tables look the following way:

table #BasicOffers

OfferId   |  Tile  | Manager  | DeliveryVersionId | 
----------+--------+----------+-------------------|
4         | Offer1 | John Doe |  1                |
5         | Offer2 | Jane Doe |  2                |

table #TmpLabels

DeliveryVersionId | Label                   |
------------------+-------------------------+
1                 | Service, Time, Material |
2                 | Consulting, Time        |

final result, unfortunately not the desired output. the temporary tables seem to be split-up again:

OfferId   | Title  | Manager  | Delivery 
----------+--------+----------+------------------------
4         | Offer1 | John Doe | Service
4         | Offer1 | John Doe | Time
4         | Offer1 | John Doe | Material
5         | Offer2 | Jane Doe | Consulting
5         | Offer2 | Jane Doe | Time

Desired Output:

OfferId   | Title  | Manager  | Delivery 
----------+--------+----------+------------------------
4         | Offer1 | John Doe | Service, Time, Material
5         | Offer2 | Jane Doe | Consulting, Time

My query to merge the tables looks like this:

-- Delivery Methods
    SELECT [D].DeliveryVersionId, [DM].Label
        INTO #TmpLabels
    FROM [MySchema].[Delivery] [D]
    INNER JOIN dbo.DeliveryMethods [DM] ON [DM].DeliveryMethodId = [D].DeliveryMethodId

    SELECT DeliveryVersionId,
        Label = STUFF(
            (SELECT ',' + Label FROM #TmpLabels FOR XML PATH('')), 1, 1, ''
        )
    FROM #TmpLabels 
    GROUP BY DeliveryVersionId

-- FinalResults
    SELECT [O].OfferId, [O].Title, [O].OfferManager, [DL].Label AS Delivery
    FROM #BasicOffers [O]
    INNER JOIN #TmpLabels [DL] ON [DL].DeliveryVersionId = [O].DeliveryVersionId

I don't really know, if it's the best solution to store everything into a temporary table and then merge it or to select it all at once.

In any case unfortunately my query seems not to work correctly.

Do you have an idea on how to solve this issue?

Thanks a lot!

Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Sean Lange, Apr 24 '17 at 13:15
you can use simple join itself to select the columns by joining offerid — Mani, Apr 24 '17 at 13:47

score 2 · Accepted Answer · 2017-04-24T14:19:02.763

;WITH BasicOffers(OfferId,Tile,Manager,DeliveryVersionId )
AS
(
SELECT 4 , 'Offer1' , 'John Doe' ,  1   Union all             
SELECT 5 , 'Offer2' , 'Jane Doe' ,  2                
)
,TmpLabels(DeliveryVersionId,Label)
AS
(
SELECT 1 , 'Service, Time, Material '  Union all  
SELECT 2 , 'Consulting, Time'        
)
Select B.OfferId,B.Tile,B.Manager, T.Label AS Delivery  
From BasicOffers B
INNER JOIN TmpLabels T
ON T.DeliveryVersionId=B.DeliveryVersionId

--To COnvert into Comma separated
;WITH Cte_Convert(OfferId,Title,Manager,Delivery )
AS
(

SELECT 4 , 'Offer1' , 'John Doe' , 'Service'    Union All
SELECT 4 , 'Offer1' , 'John Doe' , 'Time'       Union All
SELECT 4 , 'Offer1' , 'John Doe' , 'Material'   Union All
SELECT 5 , 'Offer2' , 'Jane Doe' , 'Consulting' Union All
SELECT 5 , 'Offer2' , 'Jane Doe' , 'Time'
)
Select DISTINCT OfferId,Title,Manager,STUFF((SELECT DISTINCT ',' + CAST(i.Delivery AS VARCHAR(20)) FROM Cte_Convert i
WHERE i.OfferId=o.OfferId
FOR XML PATH ('')),1,1,'')AS Delivery
FROM Cte_Convert o

 I Assume either above or below code might be useful

  --Split Delivery  column as comma separted in below Cte
 ;WITH Cte_Convert(OfferId,Title,Manager,Delivery )
AS
(
SELECT 4 , 'Offer1' , 'John Doe' , 'Service'    Union All
SELECT 4 , 'Offer1' , 'John Doe' , 'Time'       Union All
SELECT 4 , 'Offer1' , 'John Doe' , 'Material'   Union All
SELECT 5 , 'Offer2' , 'Jane Doe' , 'Consulting' Union All
SELECT 5 , 'Offer2' , 'Jane Doe' , 'Time'
)
,Final
AS
(
SELECT ROW_NUMBER()OVER(Order by (SELECT ''))AS DeliveryVersionId ,* FROM
(
SELECT  DISTINCT OfferId,Title,Manager,STUFF((SELECT DISTINCT ',' + CAST(i.Delivery AS VARCHAR(20)) FROM Cte_Convert i
WHERE i.OfferId=o.OfferId
FOR XML PATH ('')),1,1,'')AS Label
FROM Cte_Convert o
)dt
)
,BasicOffers(OfferId,Tile,Manager,DeliveryVersionId )
AS
(
SELECT 4 , 'Offer1' , 'John Doe' ,  1   Union all             
SELECT 5 , 'Offer2' , 'Jane Doe' ,  2                
)
,TmpLabels(DeliveryVersionId,Label)
AS
(
SELECT 1 , 'Service, Time, Material '  Union all  
SELECT 2 , 'Consulting, Time'        
)
Select B.OfferId,B.Tile,B.Manager, T.Label AS Delivery  
From BasicOffers B
INNER JOIN Final T
ON T.DeliveryVersionId=B.DeliveryVersionId

Ric_R · Answer 2 · 2017-04-24T13:57:23.460

I used temporary tables and Left outer join between them to solve it. Please note that I supposed that DeliveryVersionId is a primary Key in the #TmpLabels.

Please let me know if it helps you.

DECLARE @BasicOffers Table (
OfferId int,
Tile varchar(100),
Manager varchar(100),
DeliveryVersionId int)


insert into @BasicOffers values (4,'Offer1','John Doe',1)
insert into @BasicOffers values (5,'Offer2','Jane Doe',2)

DECLARE @TmpLabels Table (
DeliveryVersionId int,
Label varchar(100)
)


insert into @TmpLabels values (1,'Service, Time, Material')
insert into @TmpLabels values (2,'Consulting, Time')

Select OfferId, Tile, Manager, Label as Delivery
From @BasicOffers A Left Outer join
@TmpLabels B on A.DeliveryVersionId=B.DeliveryVersionId

T-SQL CONCATE ROW values from #Temporary Table

2 Answers2