August 12, 2012 at 7:29 am
I'm not even sure how to ask this question so don't know how to search for this in this forum, or Google it. Say I deliver goods. Every month, I track what state(s) I drive to. What I would like to do is to compress the multiple rows for each month into a single row with the states concatenated into the row.
This:
Month State
May Kansas
May Nebraska
May Iowa
June Idaho
June Montana
July Wisconsin
July Minnesota
would become this.
Month States
May Kansas,Nebraska,Iowa
June Idaho,Montana
July Wisconsin,Minnesota
I used Focus and there was a command called across. The syntax would be by month across states. Then I could concatenate the states by month pretty easily.
Any thoughts on how to do it? Thanks
August 12, 2012 at 7:44 am
suggest you search this site for "concatenate rows", crosstabs / pivots
one method that will do as you ask is by using "FOR XML PATH"
here is an example
with produce (id,fruit, qty)
as (
SELECT 101,'Apple', 'zero'
UNION ALL SELECT 101,'Banana', 'one'
UNION ALL SELECT 102,'Orange', 'two'
UNION ALL SELECT 102,'Melon' ,'three'
UNION ALL SELECT 102,'Grape' ,'four'
)
SELECT
id,
STUFF(
(
SELECT ',' + fruit + '..' + qty + ' | '
FROM produce p2
WHERE p1.id = p2.id
ORDER BY p2.fruit --- sort by Fruit name
FOR XML PATH('')),1,1,' ')
FROM produce p1
GROUP BY id
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 13, 2012 at 7:48 am
Thanks for pointing me in the right direction. Your solution along with this code I found from a blog by Anith S Larson who credits Adam Machanic with this code helped me out big time:
SELECT p1.CategoryId,
stuff( (SELECT ','+ProductName
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'')
AS Products
FROM Northwind.dbo.Products p1
GROUP BY CategoryId ;
So far, it looks great. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply