Usually pivoting data is done to aggregate data, but I had a situation at work where I wanted to return non-aggregate data.
I needed to do an audit on a table of rules that should have two identical rules; one for each of two different rule types. There were over 400 unique rules to evaluate, which means the potential of upwards of 800 records to audit.
My internal customer also wanted to know the user id and date the rule was entered. I wanted each rule to be listed once, with all relevant data for that rule in columns.
The first result in a search on how to do PIVOTS I looked at was from MSDN. The technical syntax is:
SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;
However, all the examples on this page demonstrated aggregate PIVOTS. My code on GitHub includes a temp table, sample data, and various PIVOT query versions, however, my final query is listed below.
/* Four Field Pivot Need to include the test name in a GROUP BY to get the rule to be displayed only once. */ SELECT TestName ,MIN(Type01) AS Type01 ,MIN(Usr_Type01) AS UsrType01 ,MIN(Cat_Type01) AS CatType01 ,MIN(Upd_Type01) AS UpdType01 ,'.' AS dot ,MIN(Type02) AS Type02 ,MIN(Usr_Type02) AS UsrType02 ,MIN(Cat_Type02) AS CatType02 ,MIN(Upd_Type02) AS UpdType02 FROM ( SELECT TestName ,TestType ,'Usr_' + TestType AS UsrTestType ,'Cat_' + TestType AS CatTestType ,'Upd_' + TestType AS UpdTestType ,ActFlg ,UsrID ,Category ,Upd_DT FROM #PivotDemo ) src PIVOT ( MIN(ActFlg) FOR TestType IN(Type01,Type02) ) AS pvt1 PIVOT ( MIN(UsrID) FOR UsrTestType IN(Usr_Type01,Usr_Type02) ) AS pvt2 PIVOT ( MIN(Category) FOR CatTestType IN(Cat_Type01,Cat_Type02) ) AS pvt3 PIVOT ( MIN(Upd_DT) FOR UpdTestType IN(Upd_Type01,Upd_Type02) ) AS pvt4 Group By TestName ORDER BY TestName ASC ;