CODEWITHSUNDEEP
sql-server
pickarooney
pickarooney

Reputation: 415

SQL Server Concatenate results on one line

I've been trying to do the following with STUFF but I'm missing something I think.

I have a query which returns results from joined tables in the form

DATE       | CLIENT_ID | ITEM_DESC
2017-02-01 | 12        | GLOVES
2017-02-01 | 12        | HAT
2017-02-01 | 12        | SHOES
2017-02-01 | 25        | GLOVES

and I want to reduce this by combining the ITEM_DESC into one line:

DATE       | CLIENT_ID | ITEM_DESC
2017-02-01 | 12        | GLOVES, HAT, SHOES
2017-02-01 | 25        | GLOVES

I'm trying to run a nested query on the returned results:

 select [Date] as PurDate, [client_ID], 
 AllDetails =
 STUFF (
  (select ', ' + Details  FOR XML PATH ('')), 1, 1, '' 
 ) 
from (select...

However it's just returning the exact same results. I need to group the first two columns and concatenate only what's common to that combination but that's where I'm stuck.

Upvotes: 0

Views: 2021

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

you need to add group by at the end as below:

select [Date] as PurDate, [client_ID], 
 AllDetails =
 STUFF (
  (select ', ' + Details  FOR XML PATH ('')), 1, 1, '' 
 ) 
from (select...
) a
group by [Date], [Client_Id]

Created with your sample data and you can query as below:

Select [Date], Client_id, 
    Stuff((Select ','+Item_desc from #alldata where [Date] = a.[Date] and [Client_id] = a.Client_id for xml path('')), 1,1,'')
    from #alldata a
    group by [Date], Client_id

Input table:

create table #alldata ([Date] date, Client_id int, Item_desc varchar(15))

insert into #alldata([date], Client_id, Item_desc) values
 ('2017-02-01', 12  ,'GLOVES')
,('2017-02-01', 12  ,'HAT')
,('2017-02-01', 12  ,'SHOES')
,('2017-02-01', 25  ,'GLOVES')

Output as below:

+------------+-----------+---------------------+
|    Date    | Client_id |      Item_Desc      |
+------------+-----------+---------------------+
| 2017-02-01 |        12 |  GLOVES, HAT, SHOES |
| 2017-02-01 |        25 |  GLOVES             |
+------------+-----------+---------------------+

Upvotes: 2

Related Questions