Link to home
Create AccountLog in
Avatar of allenkent
allenkentFlag for United States of America

asked on

SQL 2008 - Group Customer Name with Multi-Value in single column

I have a MS SQL view that shows customer names and state - with multiple lines of data of a value named LOT. I wish to combine all the LOT into a single column for each Customer.

Example: (this is sample of my existing SQL View.

CustomerA       CA         Lot123
CustomerA       CA         Lot456
CustomerA       CA         Lot789
CusomterZ        AZ         Lot543
CustomerZ        AZ         Lot919
CustomerV        VA        Lot777

This is what I was trying to get in my view:
CustomerA       CA         Lot123, Lot456, Lot789
CusomterZ        AZ         Lot543, Lot 919
CustomerV        VA        Lot777

This is my statement (that gives all values)
SELECT CUSTOMER,STATE,LOT

FROM dbo.MYSQLTable
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I beleive "FOR XML PATH" was around in 2008.

Try that trick?

https://sqlwithmanoj.com/2010/09/16/combine-multiple-rows-to-csv-string-and-vice-versa/
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The temp table was just to show it works as a test case.

If you have a complex query that produces the results you posted, then use a Common Table Expression (CTE):

With your_original_query as (
...your query...
)
SELECT   O.CustomerName ,
         O.StateCode ,
         STUFF((   SELECT   ', ' + I.LotCode
                   FROM     your_original_query I
                   WHERE    I.CustomerName = O.CustomerName
                            AND I.StateCode = O.StateCode
                   ORDER BY I.LotCode
                   FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,
               1 ,
               2 ,
               '') AS LotText
FROM     your_original_query O
GROUP BY O.CustomerName ,
         O.StateCode;

Open in new window