The solution that you link to would work for your case but you have to adjust it slightly because you want to pivot on multiple columns of data. Since you need to pivot multiple columns, then you first will want to unpivot your Visit Date, Task, Rf Score and Pf Score columns into multiple rows, then apply the pivot function. Besides the process of unpivoting, I would also suggest using a windowing function like row_number to generate a unique sequence for each id with date.
You will start your query by using the following:
select id, task, [visit date], [rf score], [pf score],
row_number() over(partition by id
order by [visit date]) seq
from yourtable
See SQL Fiddle with Demo. This creates a number that will be used to associate each value in Visit Date, Task, Rf Score and Pf Score with the actual number of the visit.
Once you have this row number, then you will want to unpivot your multiple columns into multiple rows of data. There are several ways to do this including using the unpivot function. But since you are using SQL Server 2008R2, you can use CROSS APPLY with VALUES:
select id,
col = col + cast(seq as varchar(10)),
value
from
(
select id, task, [visit date], [rf score], [pf score],
row_number() over(partition by id
order by [visit date]) seq
from yourtable
) d
cross apply
(
values
('VisitDate', convert(varchar(10), [visit date], 120)),
('Task', [task]),
('RfScore', cast([rf score] as varchar(10))),
('PfScore', cast([pf score] as varchar(10)))
) c (col, value)
See SQL Fiddle with Demo. Your data is now in a format that can easily be pivoted:
| ID | COL | VALUE |
|-------|------------|-----------------|
| 5180 | VisitDate1 | 2011-06-09 |
| 5180 | Task1 | Initial Visit |
| 5180 | RfScore1 | 3 |
| 5180 | PfScore1 | 9 |
| 5180 | VisitDate2 | 2011-07-08 |
| 5180 | Task2 | Follow Up Visit |
| 5180 | RfScore2 | 3 |
| 5180 | PfScore2 | 10 |
The code when the PIVOT is added willbe:
select id,
VisitDate1, Task1, RfScore1, PfScore1,
VisitDate2, Task2, RfScore2, PfScore2,
VisitDate3, Task3, RfScore3, PfScore3,
VisitDate4, Task4, RfScore4, PfScore4,
VisitDate5, Task5, RfScore5, PfScore5,
VisitDate6, Task6, RfScore6, PfScore6
from
(
select id,
col = col + cast(seq as varchar(10)),
value
from
(
select id, task, [visit date], [rf score], [pf score],
row_number() over(partition by id
order by [visit date]) seq
from yourtable
) d
cross apply
(
values
('VisitDate', convert(varchar(10), [visit date], 120)),
('Task', [task]),
('RfScore', cast([rf score] as varchar(10))),
('PfScore', cast([pf score] as varchar(10)))
) c (col, value)
) d
pivot
(
max(value)
for col in (VisitDate1, Task1, RfScore1, PfScore1,
VisitDate2, Task2, RfScore2, PfScore2,
VisitDate3, Task3, RfScore3, PfScore3,
VisitDate4, Task4, RfScore4, PfScore4,
VisitDate5, Task5, RfScore5, PfScore5,
VisitDate6, Task6, RfScore6, PfScore6)
) piv;
See SQL Fiddle with Demo.
The above works great if you have a limited number of value, but if they are unknown then you will need to use dynamic SQL and the code above will be converted to:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col + cast(seq as varchar(10)))
from
(
select row_number() over(partition by id
order by [visit date]) seq
from yourtable
) d
cross apply
(
select 'VisitDate', 1 union all
select 'Task', 2 union all
select 'RfScore', 3 union all
select 'PfScore', 4
) c (col, so)
group by seq, col, so
order by seq, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'select id, ' + @cols + '
from
(
select id,
col = col + cast(seq as varchar(10)),
value
from
(
select id, task, [visit date], [rf score], [pf score],
row_number() over(partition by id
order by [visit date]) seq
from yourtable
) d
cross apply
(
values
(''VisitDate'', convert(varchar(10), [visit date], 120)),
(''Task'', task),
(''RfScore'', cast([rf score] as varchar(10))),
(''PfScore'', cast([pf score] as varchar(10)))
) c (col, value)
) s
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. Both versions give a result:
| ID | VISITDATE1 | TASK1 | RFSCORE1 | PFSCORE1 | VISITDATE2 | TASK2 | RFSCORE2 | PFSCORE2 | VISITDATE3 | TASK3 | RFSCORE3 | PFSCORE3 | VISITDATE4 | TASK4 | RFSCORE4 | PFSCORE4 | VISITDATE5 | TASK5 | RFSCORE5 | PFSCORE5 | VISITDATE6 | TASK6 | RFSCORE6 | PFSCORE6 | VISITDATE7 | TASK7 | RFSCORE7 | PFSCORE7 | VISITDATE8 | TASK8 | RFSCORE8 | PFSCORE8 |
|-------|------------|-----------------|----------|----------|------------|-----------------|----------|----------|------------|-----------------|----------|----------|------------|-----------------|----------|----------|------------|-----------------|----------|----------|------------|-----------------|----------|----------|------------|-----------------|----------|----------|------------|-----------------|----------|----------|
| 5180 | 2011-06-09 | Initial Visit | 3 | 9 | 2011-07-08 | Follow Up Visit | 3 | 10 | 2011-09-02 | Follow Up Visit | 1 | 10 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 5199 | 2011-09-08 | Follow Up Visit | 5 | 6 | 2011-09-15 | Follow Up Visit | 2 | 7 | 2011-10-13 | Follow Up Visit | 4 | 8 | 2011-10-20 | Follow Up Visit | 2 | 4 | 2011-10-27 | Follow Up Visit | 4 | 7 | 2011-11-03 | Follow Up Visit | 3 | 3 | 2011-11-10 | Follow Up Visit | 2 | 5 | 2011-11-17 | Follow Up Visit | 3 | 4 |
| 10219 | 2013-12-26 | Follow Up Visit | 1 | 6 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
