June 10, 2013 at 7:07 pm
Hi,
I trying to get the dataset
Ptid Test Result Date
1 BP Neg 1/1/2013
1 CG Pos 1/2/2013
I want result as
Ptiid BP Date CG Date
1 Neg 1/1/2013 Pos 1/2/2013
June 10, 2013 at 7:22 pm
Group the dataset by Ptid and then use a combination of MAX() and CASE to return one value for each column, for example:
SELECT
Ptid,
BP = MAX(CASE WHEN Test = 'BP' THEN Result END),
BP_Date = MAX(CASE WHEN Test = 'BP' THEN Date END),
CG = MAX(CASE WHEN Test = 'CG' THEN Result END),
CG_Date = MAX(CASE WHEN Test = 'CG' THEN Date END)
June 10, 2013 at 7:31 pm
Thanks it works
June 10, 2013 at 10:33 pm
Out of personal curiosity, would the PIVOT command, available in SQL 2005 onwards, be of help in this situation?
http://technet.microsoft.com/en-us/library/ms177410%28v=SQL.105%29.aspx
Andre Ranieri
June 11, 2013 at 1:27 am
Andre,
I may help, however there was an article here on SSC (I think it was written by DwainC) on this subject showing the performance of PIVOT/UNPIVOT vs a CROSS TAB, and I think the Cross tab beat the pivot.
Also, from recollection PIVOT only allows you to pivot one Field, where as the requirement here is for two to be pivoted.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply