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
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
Microsoft SQL Server 2008SQL
Last Comment
slightwv (䄆 Netminder)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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):
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;
Try that trick?
https://sqlwithmanoj.com/2010/09/16/combine-multiple-rows-to-csv-string-and-vice-versa/