Friday, April 24, 2015

Convert rows to columns

Hi all,

i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this but that is not helping me out to get what i need .  any help will be appreciated .

create table #TEST_RESULTS
(ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT )

INSERT INTO #TEST_RESULTS
VALUES(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 1 ,3)
,(1,'A','2015-01-01' , 2 ,6)
,(1,'A','2015-01-01' , 2 ,9)
,(1,'A','2015-01-01' , 3 ,7)
,(1,'A','2015-01-01' , 5 ,12)
,(2,'B','2015-02-01' , 0 ,1)
,(2,'B','2015-02-01' , 4 ,3)
,(2,'B','2015-02-01' , 4,5)
,(2,'B','2015-02-01' , 3 ,8)

 SELECT ID,NAME,DOB,DAYS_SINCE_BIRTH_TO_TEST,AVG(CAST (TEST_RESULTS AS FLOAT)) AS AVG_TEST_RESULTS 
 FROM #TEST_RESULTS 
 GROUP BY ID,NAME,DOB,DAYS_SINCE_BIRTH_TO_TEST 

when i run the above sql i am getting the result set only for the days they have in the table . i want this for 5 days  ( Max days for a Patient can get ) . if they don't have the result value for that i want null in that column .  i am looking for a result set something like this .

 ID NAME      DOB             DAY_0     DAY_1       DAY_2     DAY_3      DAY_4    DAY_5
 1    A          2015-01-01    1            3               7.5            7            NULL     12
 2    B         2012-02-01     1            NULL        NULL          8             4          NULL 


 

No comments:

Post a Comment