T-SQL Using PIVOT

Question

I've been using examples from Stack Overflow to try and understand the PIVOT function, for example; Convert Rows to columns using 'Pivot' in mssql

So far I have come up with the following sample;

    CREATE table #rawTable (id int, header varchar(20), visitId int, performedWhen DateTime, recordedValue varchar(20))

    INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (10,'Blood Pressure', 1,'1 Jan 2014 10:10','110/85')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (11,'Heart Rate', 1,'1 Jan 2014 10:10','75')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (12,'Temperature', 1,'1 Jan 2014 10:10','36.9')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (10,'Blood Pressure', 2,'1 Jan 2014 14:35','120/70')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (10,'Blood Pressure', 3,'2 Jan 2014','110/80')


    DECLARE @cols AS NVARCHAR(MAX),
            @query  AS NVARCHAR(MAX)

    select @cols = STUFF((select ',' + QUOTENAME(convert(varchar(17), performedWhen, 113))
                            from #rawTable
                        group by performedWhen
                        order by performedWhen
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

    set @query = 'select header, ' + @cols + ' from 
                 (
                    select id, header, visitId, performedWhen, recordedValue
                    from #rawTable
                 ) x
                 pivot 
                 (
                     max(recordedValue)
                     for performedWhen in (' + @cols + ')
                 ) p'

    EXEC sp_executesql @query

    DROP table #rawTable

This produces the following result;

Header          01 Jan 2014 10:10  01 Jan 2014 14:35  02 Jan 2014 00:00
---------------------------------------------------------------------------
Blood Pressure  110/85             NULL               NULL
Blood Pressure  NULL               120/70         NULL
Blood Pressure  NULL               NULL               110/80
Heart Rate  75  NULL               NULL               NULL
Temperature 36.9    NULL               NULL               NULL

What I really need is this result (i.e) ALl the 'Blood Pressure' values on one line;

Header          01 Jan 2014 10:10  01 Jan 2014 14:35  02 Jan 2014 00:00
---------------------------------------------------------------------------
Blood Pressure  110/85             120/70             110/80
Heart Rate          75             NULL               NULL
Temperature         36.9               NULL               NULL

I'm not looking for the answer, just a clue to point me in the right direction as I just cannot get my head around this. :)

SQL Server version is 2012 Enterprise.

Answer 1

Try this query, I just removed the visitid from the select query

CREATE table #rawTable (id int, header varchar(20), visitId int, performedWhen DateTime, recordedValue varchar(20))

INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (10,'Blood Pressure', 1,'1 Jan 2014 10:10','110/85')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (11,'Heart Rate', 1,'1 Jan 2014 10:10','75')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (12,'Temperature', 1,'1 Jan 2014 10:10','36.9')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (10,'Blood Pressure', 2,'1 Jan 2014 14:35','120/70')
INSERT INTO #rawTable (id, header, visitId, performedWhen, recordedValue) VALUES (10,'Blood Pressure', 3,'2 Jan 2014','110/80')





DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((select ',' + QUOTENAME(convert(varchar(17), performedWhen, 113))
from #rawTable
group by performedWhen
order by performedWhen
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = 'select header, ' + @cols + ' from 
(
select id, header,  performedWhen, recordedValue
from #rawTable
) x
pivot 
(
max(recordedValue)
for performedWhen in (' + @cols + ')
) p'

EXEC sp_executesql @query
print @query

DROP table #rawTable

Answer 2

The PIVOT function automatically do something like the group by in the select clause, if you don't need the visitId field, just change your query:

set @query = 'select header, ' + @cols + ' from 
             (
                select id, header, performedWhen, recordedValue
                from #rawTable
             ) x
             pivot 
             (
                 max(recordedValue)
                 for performedWhen in (' + @cols + ')
             ) p'