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 

 

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 
 into #A
 FROM #TEST_RESULTS 
 GROUP BY ID,NAME,DOB,DAYS_SINCE_BIRTH_TO_TEST 
 
 declare @List varchar(200)
 
select @List =
STUFF((Select distinct ','+quotename('Day_'+cast(DAYS_SINCE_BIRTH_TO_TEST as varchar(20)))
FROM #TEST_RESULTS 
FOR XML PATH('')),1,1,'')
declare @SQL nvarchar(2000)
set @SQL ='select * from 
(select ID,NAME,DOB,''Day_''+cast(DAYS_SINCE_BIRTH_TO_TEST as varchar(20)) as DAYS_SINCE_BIRTH_TO_TEST ,AVG_TEST_RESULTS from #A) PT PIVOT (MIN(AVG_TEST_RESULTS)  FOR DAYS_SINCE_BIRTH_TO_TEST in (' +@LIST+')) as PS' 

Execute SP_Executesql @SQL

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 
 into #A
 FROM #TEST_RESULTS 
 GROUP BY ID,NAME,DOB,DAYS_SINCE_BIRTH_TO_TEST 
 
 declare @List varchar(200)
 --to get even missing numbers
select @List =
STUFF((select distinct ','+quotename('Day_'+cast(b.number as varchar(20))) from
(select max(Days_Since_Birth_to_test) as number FROM #TEST_RESULTS)A INNER JOIN master..spt_values  B on A.number>=B.number
where type= 'p'
FOR XML PATH('')),1,1,'')

declare @SQL nvarchar(2000)
set @SQL ='select * from 
(select ID,NAME,DOB,''Day_''+cast(DAYS_SINCE_BIRTH_TO_TEST as varchar(20)) as DAYS_SINCE_BIRTH_TO_TEST ,AVG_TEST_RESULTS from #A) PT PIVOT (MIN(AVG_TEST_RESULTS)  FOR DAYS_SINCE_BIRTH_TO_TEST in (' +@LIST+')) as PS' 

Execute SP_Executesql @SQL

>> I have a table with birth_date and test results, I am trying to pull the data from the table and convert rows columns, below is the table I am using. <<

Good SQL programmers would do this with a report writer, not with a query. You also have no key, so this is not a table at all! You also have no idea how to use ISO-11179 for data elements test_names, constraints or proper data types. A birth_date is a property of a patient, not a test. Want to include his shoe size, too? 

What is id? A patient? A magical generic thing from physical storage? If it is really an identifier, then it cannot be a numeric value! Basic data modeling defines this. 

CREATE TABLE Test_Results
(patient_id CHAR(10) NOT NULL
  REFERENCES Patients (patient_id),
 test_name VARCHAR(10) NOT NULL, 
 test_seq INTEGER NOT NULL
  CHECK(test_seq BETWEEN 1 and 5)
 test_results INTETGER NOT NULL
 CHECK(test_results >= 0),
 PRIMARY KEY (patient_id, test_name, test_seq)
);

Until we get valid DDL, it is not worth guessing at DML. 

Thanks stan!!!!!!!!!!!!!!!!!!!!  

when i tried more than 30 days , it is giving me an Error.  does this wok if we  go for 90 DAYS_SINCE_BIRTH _TO TEST ?

Thanks stan!!!!!!!!!!!!!!!!!!!!  

when i tried more than 30 days , it is giving me an Error.  does this wok if we  go for 90 DAYS_SINCE_BIRTH _TO TEST ?

add a fix..should work now..

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' , 90 ,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 
 into #A
 FROM #TEST_RESULTS 
 GROUP BY ID,NAME,DOB,DAYS_SINCE_BIRTH_TO_TEST 
 
 declare @List varchar(max)
 --to get even missing numbers
select @List =
STUFF((select distinct ','+quotename('Day_'+cast(b.number as varchar(20))) from
(select max(Days_Since_Birth_to_test) as number FROM #TEST_RESULTS)A INNER JOIN master..spt_values  B on A.number>=B.number
where type= 'p'
FOR XML PATH('')),1,1,'')

declare @SQL nvarchar(max)
set @SQL ='select * from 
(select ID,NAME,DOB,''Day_''+cast(DAYS_SINCE_BIRTH_TO_TEST as varchar(20)) as DAYS_SINCE_BIRTH_TO_TEST ,AVG_TEST_RESULTS from #A) PT PIVOT (MIN(AVG_TEST_RESULTS)  FOR DAYS_SINCE_BIRTH_TO_TEST in (' +@LIST+')) as PS' 

Execute SP_Executesql @SQL


drop table #TEST_RESULTS 
drop Table #A

I Hope you might have noticed it  result set is not in the order ,  i mean  it is starting with day_0 then day_1  instead of day_2 it is giving the day_11 .I tried different ways but it did not work out  can you help me with this  .... thanks in advance  

I Hope you might have noticed it  result set is not in the order ,  i mean  it is starting with day_0 then day_1  instead of day_2 it is giving the day_11 .I tried different ways but it did not work out  can you help me with this  .... thanks in advance  

try this

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' , 90 ,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 
 into #A
 FROM #TEST_RESULTS 
 GROUP BY ID,NAME,DOB,DAYS_SINCE_BIRTH_TO_TEST 
 
 declare @List varchar(max)
 --to get even missing numbers
select @List =
STUFF((select  ','+quotename('Day_'+cast(b.number as varchar(20))) from
(select max(Days_Since_Birth_to_test) as number FROM #TEST_RESULTS)A INNER JOIN master..spt_values  B on A.number>=B.number
where type= 'p' order by b.number
FOR XML PATH('')),1,1,'')

declare @SQL nvarchar(max)
set @SQL ='select * from 
(select ID,NAME,DOB,''Day_''+cast(DAYS_SINCE_BIRTH_TO_TEST as varchar(20)) as DAYS_SINCE_BIRTH_TO_TEST ,AVG_TEST_RESULTS from #A) PT PIVOT (MIN(AVG_TEST_RESULTS)  FOR DAYS_SINCE_BIRTH_TO_TEST in (' +@LIST+')) as PS' 

Execute SP_Executesql @SQL


drop table #TEST_RESULTS 
drop Table #A

Good SQL programmers would do this with a report writer,

Good SQL programmers don't know how to use a report writer! (Unless with "writer", you are referring to the developer who is responsible for the report design, of course.)