Prev: What is the easiest way to empty all tables of my database?
Next: Place varchar fields at the end of table for optimum performan
From: Lito on 2 Nov 2009 20:12
I know the limitation of PIVOT (SQL2005) ... just curious as to any tricks
out there available using multiple pivot keys.

SELECT * FROM TEMP_LABRESULTS

pat_id labid collection_date value
1233 1 2009-01-04 7
1233 2 2009-01-04 123
1233 3 2009-01-04 88
1234 2 2009-01-6 127
1234 3 2009-01-6 99
1245 1 2009-01-12 8
1266 1 2009-02-01 9
1266 2 2009-02-09 119
1266 3 2009-02-09 121

SELECT pat_id, [1] AS 'value1', [2] AS 'value2', [3] AS 'value3'
FROM TEMP_LABRESULTS
PIVOT(MAX(LABVALUE) FOR labid
IN([1],[2],[3])) AS P
ORDER BY pat_id

pat_id value1 value2 value3
1233 7 123 88
1234 NULL 127 99
1245 8 NULL NULL
1266 9 NULL NULL
1266 NULL 119 121

Now, to add collection date, I have to use MAX CASE GROUP BY since PIVOT has
limitation ... is there a way to do this in PIVOT using some tricks or I am
better off using MAX CASE GROUP BY?

SELECT pat_id, MAX( CASE labid WHEN 1 THEN labvalue END) AS 'Value1',
MAX( CASE labid WHEN 1 THEN collectiondate END) 'Value1
Collection Date',
MAX( CASE labid WHEN 2 THEN labvalue END) AS 'Value2',
MAX( CASE labid WHEN 2 THEN collectiondate END) AS 'Value2
Collection Date',
MAX( CASE labid WHEN 3 THEN labvalue END) AS 'Value3',
MAX( CASE labid WHEN 3 THEN collectiondate END) AS 'Value3
Collection Date'
FROM TEMP_LABRESULTS
GROUP BY pat_id

1233 7 2009-01-04 123 2009-01-04 88 2009-01-04
1234 NULL NULL 127 2009-01-6 99 2009-01-6
1245 8 2009-01-12 NULL NULL NULL NULL
1266 9 2009-02-01 119 2009-02-09 121 2009-02-09
 | 
Pages: 1
Prev: What is the easiest way to empty all tables of my database?
Next: Place varchar fields at the end of table for optimum performan