Transposing data into columns from rows

Question

I have a data set where it goes something similar to this:

name    measure    status
john doe    blood pressure    iscompliant
john doe    Hba1c             notcompliant
jane doe    BMI               iscompliant
jane doe    blood pressure    notcompliant

I will have an unknown amount of measures to work with, so if there's a way to do this dynamically, that would be awesome. I'm looking for a result set like this:

name      blood pressure    Hba1c          BMI       
john doe  iscompliant       notcompliant   null   
jane doe  notcompliant      null           iscompliant

I couldn't find any examples that were able to tie two columns together like in my example with status and measure. I'm not sure if if I need to cross apply my data then pivot, or if I can pivot straight away. Any help is appreciated, thank you for your time.

edit: i am using sql server 2014

`case` expressions. Here's an example from a quick search: https://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when — shawnt00, Mar 02 '18 at 16:38
You can also use `pivot` https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Hackerman, Mar 02 '18 at 16:46
@shawnt00 I wanted to avoid case expressions since I have an unknown number of measures in the data. My example is simplified for readability. — Angus Gray, Mar 02 '18 at 16:51
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — JNevill, Mar 02 '18 at 17:30
@AngusGray, here's another resource on a [dynamic `PIVOT` query](https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/) — BJones, Mar 02 '18 at 18:32

score 0 · Accepted Answer · answered Mar 02 '18 at 22:06

I eventually figured it out myself. This isn't the real solution I am using, but a solution using the example data I posted above so people can reference this solution in the future.

 DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
            DECLARE @ColumnName AS NVARCHAR(MAX);




--Get distinct values of the PIVOT Column 
            SELECT  @ColumnName = ISNULL(@ColumnName + ',' , '')
                    + QUOTENAME(Measure)
            FROM    ( SELECT DISTINCT
                                Measure
                      FROM      #temptable
                    ) AS qmdata;

--Prepare the PIVOT query using the dynamic 
            SET @DynamicPivotQuery = N'SELECT name, '
                + @ColumnName + '
                into workdb.dbo.qm_data_test
    FROM #temptable
    PIVOT(max(status)
          FOR measure IN (' + @ColumnName + ')) AS PVTTable';
--Execute the Dynamic Query
            EXEC sp_executesql @DynamicPivotQuery;

Once I had it in the workdb, I ran this to de-duplicate the data:

SELECT  name
      ,MAX(measure1)
      ,MAX(measure2)
      ,MAX(measure3)
  FROM [workdb].[dbo].[qm_data_test]
  GROUP BY name

I am about to write the dynamic sql for the second query so I don't have to manually code each occurrence of MAX(ColumnName), so if a measure is added, my code will pick it up.

Transposing data into columns from rows

1 Answers1