Summarize Rows to Field
-
I have a table with the following structure:
# Word
326 Aspen
326 Oak
326 Willow
218 June
218 Fern
736 Birch
155 Game
155 Hill
155 Fir
etc., etc.
What I need to do is summarize the data in the following form:
326 Aspen, Oak, Willow
218 June, Fern
736 Birch
155 Game, Hill, Fir
I have tried a variety of things and nothing seems to be getting me where I want to go. I'm sure this is easy but I am not seeing it. It wouldn't be a problem if I was creating a list from the whole table, but I can't get it to work with the grouped data.
Any help would be appreciated.
Thanks,
RedEye.
-
See if this helps: http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You could try creating a UDF like the one below;
CREATE FUNCTION dbo.f_get_item_codes (@item nvarchar(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @codes VARCHAR(256)
SELECT @codes = '' /*eliminating NULL value*/
SELECT @codes = @codes + it.code + ','
FROM TABLE1 it
WHERE it.word = @item
ORDER BY it.code /*to order colors alphabetically*/
/*strip away the delimiter after last color*/
IF @codes <> '' SELECT @codes = (LEFT(@codes,LEN(@codes)-1))
RETURN @codes
END
Then, you can query it like any other table/view. Using;
Select *, dbo.f_get_item_codes(Table.PK_id) as com_codes FROM Table
Hope this helps
-
Thanks for these responses. They have helped clarify the logic...and I like the UDF approach, but I am using SQL 7 and cannot create UDF's so I have adapted the temp table approch from Rob Volk in the SQLJunkies.com post.
It works perfectly...wish I had thought of it.
OK, problem solved, but is there any other way to do this without UDF's?? Without temp tables?
Thanks again for the feedback!
Rob.
-
OK, problem solved, but is there any other way to do this without UDF's?? Without temp tables?
Yes, do this at the client.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Don't forget, Frank, you could also do it with a cursor 🙂
--
Adam Machanic
whoisactive -
Brilliant idea, Adam!!! May I suggest, that we can improve this even further? How about making it generic and use dynamic sql here?
What about this?
USE Northwind
GO
DECLARE @delimiter CHAR
SET @delimiter = ', '
SELECT
CustomerID
, MAX(CASE WHEN [rank] = 12 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 11 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 10 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 9 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 8 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 7 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 6 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 5 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 4 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 3 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 2 THEN [mark]+@delimiter ELSE '' END)
+ MAX(CASE WHEN [rank] = 1 THEN [mark] ELSE '' END)
AS Orders
FROM
(
SELECT
O1.CustomerID
, COUNT(O2.OrderID) AS [rank]
, CAST(O1.OrderID AS VARCHAR(5)) [mark]
FROM
Orders O1
JOIN
Orders O2
ON
O2.CustomerID = O1.CustomerID
AND
O2.OrderID <= O1.OrderID
GROUP BY
O1.CustomerID
, CAST(O1.OrderID AS VARCHAR(5))
) O
GROUP BY
CustomerID
ORDER BY
CustomerID
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply