Git and GitHub

Beginning a new journey with Git and GitHub. Haven’t picked a project yet, but look forward to building my library. My handle is … drum roll … SerenitySQL.

How to PIVOT non-aggregate data

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>  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
    <aggregation function>(<column being aggregated>)  
[<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
                ,'Usr_' + TestType AS UsrTestType
                ,'Cat_' + TestType AS CatTestType
                ,'Upd_' + TestType AS UpdTestType

        FROM    #PivotDemo 
        ) src

        (   MIN(ActFlg)
            FOR TestType IN(Type01,Type02)
        ) AS pvt1        

        (   MIN(UsrID)
            FOR UsrTestType IN(Usr_Type01,Usr_Type02)
        ) AS pvt2

        (   MIN(Category)
            FOR CatTestType IN(Cat_Type01,Cat_Type02)
        ) AS pvt3

        (   MIN(Upd_DT)
            FOR UpdTestType IN(Upd_Type01,Upd_Type02)
        ) AS pvt4

Group By TestName