March 25, 2009 at 10:22 am
Hi guys,
I am using PIVOT to transpose certain values in a column.
Ex:
--
-- Patient Table
--
[ID] [Patient Name] [Age]
1 John 23
2 Jack 34
--
-- Item Table
--
[ID] [Patient_ID] [Item Type] [Value]
1 1 Favourite Color Yellow
2 1 Favourite Color Blue
3 1 Favourite Color Black
4 1 Favourite Movie Good Will Hunting
5 1 Favourite Movie Forrest Gump
6 1 Favourite Movie Kid
7 2 Favourite Color Red
8 2 Favourite Color Blue
9 2 Favourite Movie Miracle Worker
10 2 Favourite Movie Some Movie
11 2 Favourite Movie Other Movie
--
--With PIVOT, using MAX() in the Aggregate function I was able to get out put like this
--
[ID] [Patient Name] [Age] [Fav. Color] [Fav. Movie]
1 John 23 Yellow Kid
2 Jack 34 Red Some Movie
--
-- But I want output like, with string concatenation
--
[ID] [Patient Name] [Age] [Fav. Color] [Fav. Movie]
1 John 23 Yellow, Blue, Black Good Will Hunting, Kid, Forrest Gump
2 Jack 34 Red, Blue Miracle Worker, Some Movie, Other Movie
I want the output from PIVOT concatenated. I wanted to write a function that does string concatenation, but table-varaible parameter is NOT allowed in Sql 2005. [Its allowed in Sql 2008]
Please advise,
_U
--
-- PIVOT I used: Not the exact query, but to give an idea
--
; WITH PvtCTE AS (query the item table with patient_uid)
SELECT * FROM (SELECT FROM Patient Table INNER JOIN with PvCTE ON Patient_ID)
PIVOT (
MAX(value)
FOR item_type IN ([Favourite Color], [Favourite Movie])
)
March 25, 2009 at 10:33 am
You want to look up custom AGGREGATE functions in 2005's BOL. The CLR sample provided is a concatenation function.
In order to use a function in the PIVOT, it needs to be treated as an aggregate function, which means special handling.
I don't have the direct link handy - but if you cannot find it, post a response and I will go digging for it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2009 at 11:59 am
I think there migght be another way to skin this cat...
If I could get the [item type] table results in a concatenated format in the CTE, that would pretty much slove this problem.
Something like:
; WITH PvtCTE AS (SELECT Patient_ID, Item_Type, fun_String_Concat(Value) FROM Item_Type GROUP BY Patient_ID, Item_Type)
SELECT * FROM (SELECT FROM Patient Table INNER JOIN with PvCTE ON Patient_ID)
PIVOT (
MAX(value)
FOR item_type IN ([Favourite Color], [Favourite Movie])
)
If you have any sample string concact functions, please share....
Matt,
CLR sounds interesting. I've never tried CLR's before. So something new to learn for me.
thanks all,
_U
March 25, 2009 at 12:48 pm
In the spirit of teaching you how to fish - here is a great article on conatenation options:
http://www.sqlservercentral.com/articles/Test+Data/61572/[/url]
Similary - here's the CLR sample aggregate I was mentioning:
http://msdn.microsoft.com/en-us/library/ms131056.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2009 at 2:10 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply