Merge data into one row

• deecastle

  SSC Journeyman

  Points: 93

  More actions

  November 3, 2015 at 11:19 pm

  #312749

  Hello there, I am currently writing a query to show the quote number and its brand names inside that quote which should be separated with "/" if there are different brands in a quote. Please see below.

  QuoteDetail Table:

  QuoteNum | Brand

  10047 | NISSAN

  10048 | TOYOTA

  10049 | TOYOTA

  10050 | BOBCAT

  10050 | JOHN DEERE

  10050 | KAWASAKI

  10050 | MANITOU

  10050 | POLARIS

  10050 | SKYLARK

  10051 | DOOSAN

  10051 | MITSUBISHI

  Result:

  QuoteNum | Brand

  10047 | NISSAN

  10048 | TOYOTA

  10049 | TOYOTA

  10050 | BOBCAT/JOHN DEERE/KAWASAKI/MANITOU/POLARIS/SKYLARK

  10051 | DOOSAN/MITSUBISHI

  Please help me find the correct query. I tried some functions which I found in the internet but it didn't work. Thanks in advance.

  Regards,

  Dee

• Ant-Green

  SSC Guru

  Points: 113509

  More actions

  November 4, 2015 at 2:03 am

  #1837542

  This produces the result set required.

  create table #quotedetail (QuoteNum int, Brand varchar(10))

  INSERT INTO #quotedetail values

  (10047,'NISSAN'),

  (10048,'TOYOTA'),

  (10049,'TOYOTA'),

  (10050,'BOBCAT'),

  (10050,'JOHN DEERE'),

  (10050,'KAWASAKI'),

  (10050,'MANITOU'),

  (10050,'POLARIS'),

  (10050,'SKYLARK'),

  (10051,'DOOSAN'),

  (10051,'MITSUBISHI')

  select * from #quotedetail

  Select distinct QD2.QuoteNum,

  substring(

  (

  Select '/'+QD1.Brand AS [text()]

  From #quotedetail QD1

  Where QD1.QuoteNum = QD2.QuoteNum

  ORDER BY QD1.QuoteNum

  For XML PATH ('')

  ), 2, 1000) [Brands]

  From #quotedetail QD2