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_51 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