Combining multiple column values into a single column row result

Question

I have a query that pulls in group sale orders for a venue. My current query pulls all of the info I need, but gives me multiple rows for each order number:

ordCode cltClientName          adrCityDesc  adrStateDesc    adrZipCode  gsnValue2   evShow  shDescr
739802  Stevens Elementary,    Aberdeen         WA             98520       64        183    Group - Museum Admission
739802  Stevens Elementary,    Aberdeen         WA             98520       64        237    Extreme Planets

What I would like to see is a single row for each order, but with the last column having results separated by comma:

ordCode cltClientName          adrCityDesc  adrStateDesc    adrZipCode  gsnValue2   evShow  shDescr
739802  Stevens Elementary,    Aberdeen         WA             98520       64        183    Group - Museum Admission, Extreme Planets

The Query:

SELECT DISTINCT(ordCode),
        cltClientName, 
        adrCityDesc,
        adrStateDesc,
        adrZipCode,
        gsnValue2,
        evShow,
        shDescr

--COUNT(tiCode)
  from Orders 
        inner join OrderContacts on orcOrderNumber = ordCode
        inner join Clients on orcClientID = cltCode
        inner join ClientAddresses on cltCode = claClientCode
        inner join Addresses on claAddressCode = adrCode
        inner join Tickets on tiOrder = ordCode
        inner join dbo.Events on tiEvent = evCode
        inner join GroupSaleNotes on ordCode = gsnOrderNumber
        inner join Shows on evShow = shCode
 WHERE ordOrderTypeTitle = 7 -- Group Sales only
  and ordOpenDate between '2011-06-01 00:00:00.000' and '2012-05-31 23:59:59.999'
  and gsnNoteType = '5'
  and cltIsGroupLeader = '1'
   --and evShow in (106, 107)
 GROUP BY ordCode, cltClientName, cltCode, adrCityDesc, adrStateDesc,
      adrZipCode, gsnValue2, evShow, shDescr
ORDER BY adrCityDesc, cltClientName

Is there a way to combine this part of the result set?

You have two rows with unique evShow values. Is shDescr from the Shows table? You should also look at this: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Ian Thompson, Oct 22 '12 at 22:09

Taryn · Answer 1 · 2012-10-22T22:19:14.787

You previously had a question tagged with MySQL so a solution for MySQL would be using GROUP_CONCAT():

SELECT ordCode,
  cltClientName, 
  adrCityDesc,
  adrStateDesc,
  adrZipCode,
  gsnValue2,
  group_concat(evShow),
  group_concat(shDescr)
from Orders 
inner join OrderContacts 
  on orcOrderNumber = ordCode
inner join Clients 
  on orcClientID = cltCode
inner join ClientAddresses 
  on cltCode = claClientCode
inner join Addresses 
  on claAddressCode = adrCode
inner join Tickets 
  on tiOrder = ordCode
inner join dbo.Events 
  on tiEvent = evCode
inner join GroupSaleNotes 
  on ordCode = gsnOrderNumber
inner join Shows 
  on evShow = shCode
WHERE
  ordOrderTypeTitle = 7 -- Group Sales only
  and ordOpenDate between '2011-06-01 00:00:00.000' and '2012-05-31 23:59:59.999'
  and gsnNoteType = '5'
  and cltIsGroupLeader = '1'
  --and evShow in (106, 107)
GROUP BY ordCode,
  cltClientName, 
  cltCode,
  adrCityDesc,
  adrStateDesc,
  adrZipCode,
  gsnValue2
ORDER BY adrCityDesc,
  cltClientName

This applies the GROUP_CONCAT() to both the evShow and shDescr columns since you have multiple values for both of those.

In SQL Server, you can use something similar to this (untested):

SELECT ordCode,
  cltClientName, 
  adrCityDesc,
  adrStateDesc,
  adrZipCode,
  gsnValue2,
  stuff((select distinct ', ' + cast(e.evShow as varchar(10))
         from dbo.events e
         where tiEvent = evCode
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') evShow,
  stuff((select distinct ', ' + cast(e.shDescr as varchar(10))
         from dbo.events e
         where tiEvent = evCode
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') evShow,
from Orders 
inner join OrderContacts 
  on orcOrderNumber = ordCode
inner join Clients 
  on orcClientID = cltCode
inner join ClientAddresses 
  on cltCode = claClientCode
inner join Addresses 
  on claAddressCode = adrCode
inner join Tickets 
  on tiOrder = ordCode
--inner join dbo.Events 
--  on tiEvent = evCode
inner join GroupSaleNotes 
  on ordCode = gsnOrderNumber
inner join Shows 
  on evShow = shCode
WHERE
  ordOrderTypeTitle = 7 -- Group Sales only
  and ordOpenDate between '2011-06-01 00:00:00.000' and '2012-05-31 23:59:59.999'
  and gsnNoteType = '5'
  and cltIsGroupLeader = '1'
  --and evShow in (106, 107)
GROUP BY ordCode,
  cltClientName, 
  cltCode,
  adrCityDesc,
  adrStateDesc,
  adrZipCode,
  gsnValue2
ORDER BY adrCityDesc,
  cltClientName

This is close. The last column now contains a comma-separated list of _every_ value in the shDescr column. I've tried playing with it, but no luck. Any thoughts? — StephenYo, Oct 23 '12 at 00:12
@StephenYo you will have to play with the joins for those tables, you might have to add additional tables in the `STUFF()` code. It is difficult to tell what data is coming from each table since you did not preface the fields with a table name or alias. — Taryn, Oct 23 '12 at 00:23

Combining multiple column values into a single column row result

1 Answers1