Merging data with different fields [duplicate]

Question

Possible Duplicate:
Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server)

Basicly there are some same data with some different fields. And our client wants to merge that kind of data. I've shown it below.

season_id | service_id | service_name | product_line | service_date
10-12/11  | PIM0768    | Hilton NYC   | H            | 2012-03-02
10-12/11  | PIM0954    | Hilton NYC   | AB1          | 2012-03-02 


season_id | service_id         | service_name | product_line | service_date
10-12/11  | PIM0768,PIM0954    | Hilton NYC   | H,AB1        | 2012-03-02

How can i do that?

bummi · Accepted Answer · 2012-12-14 07:51:58Z

1

CREATE TABLE #tmp (season_id varchar(10), service_id Varchar(10), service_name varchar(30), product_line varchar(10), service_date datetime)

INSERT INTO #tmp  VALUES ('10-12/11','PIM0768','Hilton NYC','H','20120302')
INSERT INTO #tmp  VALUES ('10-12/11','PIM0954','Hilton NYC','ABC','20120302')

SELECT 
  season_id,
  cast(
  STUFF((
    SELECT ', '  +  service_id 
    FROM #tmp 
    WHERE (season_id = Results.season_id) 
    FOR XML PATH (''))
  ,1,2,'')as Varchar(max)) AS service_id
  ,service_name
  ,Cast(STUFF((
    SELECT ', ' + product_line
    FROM #tmp 
    WHERE (season_id = Results.season_id) 
    FOR XML PATH (''))
  ,1,2,'')as Varchar(max)) AS product_line
  ,service_date  
FROM #tmp Results
GROUP BY season_id,service_name,service_date

Drop table #tmp

edited Dec 14, 2012 at 7:51

answered Dec 14, 2012 at 7:33

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging data with different fields [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related