UnPivot

SQLScott · August 6th, 2007, 02:10 PM

Oh ye wise and all knowing SQL gurus. I come before thee seeking thine SQL wisdom! (How's that for butt kissing!) :)

OK, seriously, I need your advise on how to approach a problem. I originally thought the UnPivot operator would do it, but I'm not sure.

Right now, I am working with two standard, normalized, tables which contains activity information. The first table is the main Activity table:

ActivityID, StartTime, EndTime, CategoryID, TypeID, Subject, blah

The second table is an ActivityHistory table:

ActivityHistoryID, ActivityID, StartTime, ...blah

As the Activity record is changed, a record is writting to this history table.

What I would like to do is to return the records from the history table as shown below:

ActivityID Field OldValue NewValue
---------- ----- -------- --------
1 Starttime 1:00 2:00
1 Endtime 2:00 3:00
1 ActivityID 3 5
1 TypeID 10 15
2 Starttime 3:00 4:00
2 Endtime 4:00 5:00
2 ActivityID 3 6
2 TypeID 10 2
...
...

The reason for this is I would like to display it in the UI as such. Meaning, within a grid:

Field OldValue NewValue
----- -------- --------
Starttime 1:00 2:00
Endtime 2:00 3:00
ActivityID 3 5
TypeID 10 15
Starttime 3:00 4:00
Endtime 4:00 5:00
ActivityID 3 6
TypeID 10 2

In a format that they can drill down for a given activity and see the history.

So, I think the UnPivot can give me this but I have been unable to get it to return the data as I want it above. Any thoughts?

Thanks...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Peso · August 18th, 2007, 05:43 PM

Code:

-- Prepare sample data
DECLARE    @Sample TABLE (ActivityHistoryID INT, StartTime VARCHAR(5), EndTime VARCHAR(5), ActivityID VARCHAR(12), TypeID VARCHAR(12))

INSERT    @Sample
SELECT    1, '1:00', '2:00', '3', '10' UNION ALL
SELECT    1, '2:00', '3:00', '5' ,'15' UNION ALL
SELECT    1, '7:00', '8:00', '99' ,'-5' UNION ALL
SELECT    2, '3:00', '4:00', '3' ,'10' UNION ALL
SELECT    2, '4:00', '5:00', '6' ,'2'

-- Stage the data
;WITH cte (ActivityHistoryID, StartTime, EndTime, ActivityID, TypeID, RecID)
AS (
    SELECT        ActivityHistoryID,
            StartTime,
            EndTime,
            ActivityID,
            TypeID,
            ROW_NUMBER() OVER (PARTITION BY ActivityHistoryID ORDER BY CAST(StartTime AS DATETIME)) - 1 AS RecID
    FROM        @Sample
)

-- Show the expected output
SELECT        ActivityHistoryID,
        Field,
        MAX(CASE WHEN RecID % 2 = 0 THEN Value ELSE '' END) AS OldValue,
        MAX(CASE WHEN RecID % 2 = 1 THEN Value ELSE '' END) AS NewValue
FROM        (
            SELECT        ActivityHistoryID,
                    RecID,
                    1 AS SortOrder,
                    'StartTime' AS Field,
                    StartTime AS Value
            FROM        cte
            UNION ALL
            SELECT        ActivityHistoryID,
                    RecID,
                    2,
                    'EndTime',
                    EndTime
            FROM        cte
            UNION ALL
            SELECT        ActivityHistoryID,
                    RecID,
                    3,
                    'ActivityID',
                    ActivityID
            FROM        cte
            UNION ALL
            SELECT        ActivityHistoryID,
                    RecID,
                    4,
                    'TypeID',
                    TypeID
            FROM        cte
        ) AS d
GROUP BY    ActivityHistoryID,
        RecID / 2,
        Field,
        SortOrder
ORDER BY    ActivityHistoryID,
        RecID / 2,
        SortOrder

SQLScott · September 26th, 2007, 01:16 PM

Hi Peso,

I apologize for the late reply, but I was pulled off onto something else and am now just getting back to this. Your solution is awesome, and works fantastic, except for one small detail.

The results is not quite correct, in that, there are more changes that need to be reported. Your example code provided the following data (for ActivityHistoryID 1):

1, '1:00', '2:00', '3', '10'
1, '2:00', '3:00', '5' ,'15'
1, '7:00', '8:00', '99' ,'-5'

When I run the code, I get the following output:

ActivityHistoryID Field OldValue NewValue
---------------------------------------------------------
1 StartTime 1:00 2:00
1 EndTime 2:00 3:00
1 ActivityID 3 5
1 TypeID 10 15
1 StartTime 7:00
1 EndTime 8:00
1 ActivityID 99
1 TypeID -5

This is not quite correct, in that, there should be more entries. To me, the results should be as follows:

ActivityHistoryID Field OldValue NewValue
---------------------------------------------------------
1 StartTime 1:00 2:00
1 EndTime 2:00 3:00
1 ActivityID 3 5
1 TypeID 10 15
1 StartTime 2:00 7:00
1 EndTime 3:00 8:00
1 ActivityID 5 99
1 TypeID 15 -5

The reasoning behind this is that when the record for ActivityHistoryID 1 is changed a second time, the new values of the first change become the old values of the second change, and so on for subsequent changes (the new values of the previous record become the new values for the new record). So there should never be an NULL "new value" value for any field.

Does this make sense?

Again, I think your solution is wonderful, but I cannot get it to provide the data as I would like. I'm hoping there is just a small change to the code but nothing I am doing is giving me the right results.

Any thoughts?

Thank you very much.

Scott

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Peso · September 26th, 2007, 02:45 PM

Try to provide proper sample data in this style

INSERT @Sample
SELECT 1, '1:00', '2:00', '3', '10' UNION ALL
SELECT 1, '2:00', '3:00', '5' ,'15' UNION ALL
SELECT 1, '7:00', '8:00', '99' ,'-5' UNION ALL
SELECT 2, '3:00', '4:00', '3' ,'10' UNION ALL
SELECT 2, '4:00', '5:00', '6' ,'2'

and also your expected output based on your provided sample data.

SQLScott · September 26th, 2007, 03:17 PM

OK, here is actual data from the ActivityHistory table (hopefully the web page won't mess with the formating):

ActHistID ActivityID StartTime EndTime ActivityCategoryID ActivityTypeID
----------- -------------------- ----------------------- ----------------------- ------------------- ----------------------
1 163 2007-09-24 23:00:00.000 2007-09-24 00:00:00.000 10006 10022
3 163 2007-09-24 01:00:00.000 2007-09-24 02:00:00.000 10007 10023
4 163 2007-09-24 02:00:00.000 2007-09-24 03:00:00.000 10008 10024
5 163 2007-09-24 03:00:00.000 2007-09-24 04:00:00.000 10009 10025

there are four history records, which means that the parent record has an original value (ActHistID 1) and has been changed 3 times (ActHistID 3, 4, and 5). Based on my earlier comments, I would like to see the following:

ActHistID Field OldValue NewValue
-------------------------------------------
1 StartTime 23:00 PM 1:00 AM
1 EndTime 00:00 AM 2:00 AM
1 ActivityID 10006 10007
1 TypeID 10022 10023
1 StartTime 1:00 2:00
1 EndTime 2:00 3:00
1 ActivityID 10007 10008
1 TypeID 10023 10024
1 StartTime 2:00 3:00
1 EndTime 3:00 4:00
1 ActivityID 10008 10009
1 TypeID 10024 10025

the first OldValue is the original (first) value. The NewValue then becomes the next OldValue, and so on.

Hopefully makes things a little clearer...

Greatly appreciated...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Peso · September 27th, 2007, 03:11 AM

Here you go!

-- Prepare sample data
DECLARE @Sample TABLE (ActHistID INT, ActivityID INT, StartTime DATETIME, EndTime DATETIME, ActivityCategoryID INT, ActivityTypeID INT)

INSERT @Sample
SELECT 1, 163, '2007-09-24 23:00', '2007-09-24 00:00', 10006, 10022 UNION ALL
SELECT 3, 163, '2007-09-24 01:00', '2007-09-24 02:00', 10007, 10023 UNION ALL
SELECT 4, 163, '2007-09-24 02:00', '2007-09-24 03:00', 10008, 10024 UNION ALL
SELECT 5, 163, '2007-09-24 03:00', '2007-09-24 04:00', 10009, 10025

-- Set up staging expression
;WITH theData (ActHistId, theColumn, theValue, theOrder)
AS (
        SELECT ActHistID,
                theColumn,
                theValue,
                theOrder
        FROM (
                        SELECT ActHistID,
                                'StartTime' AS theColumn,
                                CONVERT(CHAR(5), StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2) AS theValue,
                                1 AS theOrder
                        FROM @Sample

                        UNION ALL

                        SELECT ActHistID,
                                'EndTime' AS theColumn,
                                CONVERT(CHAR(5), EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2) AS theValue,
                                2 AS theOrder
                        FROM @Sample

                        UNION ALL

                        SELECT ActHistID,
                                'ActivityCategoryID' AS theColumn,
                                CONVERT(CHAR(20), ActivityCategoryID) AS theValue,
                                3 AS theOrder
                        FROM @Sample

                        UNION ALL

                        SELECT ActHistID,
                                'ActivityTypeID' AS theColumn,
                                CONVERT(CHAR(20), ActivityTypeID) AS theValue,
                                4 AS theOrder
                        FROM @Sample
                ) AS d
), theControl (FromID, ToID)
AS (
        SELECT s1.ActHistID AS FromID,
                        MIN(s2.ActHistID) AS ToID
        FROM @Sample AS s1
        INNER JOIN @Sample AS s2 ON s2.ActHistID > s1.ActHistID
        GROUP BY s1.ActHistID
)

-- Show the expected output
SELECT d1.ActHistID,
                d1.theColumn AS Field,
                d1.theValue AS OldValue,
                d2.theValue AS NewValue
FROM theControl AS c
INNER JOIN theData AS d1 ON d1.ActHistID = c.FromID
INNER JOIN theData AS d2 ON d2.ActHistID = c.ToID
WHERE d1.theColumn = d2.theColumn
ORDER BY c.FromID,
                d1.theOrder

Oh, I would love to get a hand on your book Professional SQL Server 2005 XML

;)

poojafalor · September 27th, 2007, 08:48 AM

Here one more way to get the required output.

-- Prepare sample data
DECLARE @Sample TABLE (ActHistID INT,ActivityID INT, StartTime DateTime, EndTime DateTime, ActivityCategoryID VARCHAR(20), ActivityTypeID VARCHAR(20))

INSERT @Sample
SELECT 1, 163, '2007-09-23 23:00:00.000', '2007-09-24 00:00:00.000', '10006', '10022' UNION ALL
SELECT 3, 163, '2007-09-24 01:00:00.000', '2007-09-24 02:00:00.000', '10007', '10023' UNION ALL
SELECT 4, 163, '2007-09-24 02:00:00.000', '2007-09-24 03:00:00.000', '10008', '10024' UNION ALL
SELECT 5, 163, '2007-09-24 03:00:00.000', '2007-09-24 04:00:00.000', '10009', '10025'

-- Push data to CTE with a serially generated Record Id for each ActivityId
;WITH cte (ActivityID, ActHistID, StartTime, EndTime, CategoryID, TypeID, RecID)
AS (
    SELECT ActivityID,
            ActHistID,
            CAST(CONVERT(VARCHAR(5), StartTime, 108) + ' ' + RIGHT(CONVERT(VARCHAR(19), StartTime, 100), 2) AS VARCHAR(20)),
            CAST(CONVERT(VARCHAR(5), EndTime, 108) + ' ' + RIGHT(CONVERT(VARCHAR(19), EndTime, 100), 2) AS VARCHAR(20)),
            ActivityCategoryID,
            ActivityTypeID,
            ROW_NUMBER() OVER (PARTITION BY ActivityID ORDER BY StartTime) - 1 AS RecID
    FROM @Sample
)

-- Forming the query to return the expected output
SELECT ActHistId, Field, OldValue, newValue
FROM (
SELECT Old.RecId RecId, (CASE Old.Field
                WHEN 'StartTime' THEN 1
                WHEN 'EndTime' THEN 2
                WHEN 'CategoryID' THEN 3
                WHEN 'TypeID' THEN 4 END) FieldDisplaySequence
    , Old.ActivityID, 1 ActHistID, Old.Field, Old.FieldValue OldValue, New.FieldValue NewValue
FROM
    (select ActivityID, ActHistID, Field, FieldValue, RecID
     from cte
     UNPIVOT ( FieldValue for Field IN ([StartTime], [EndTime], [CategoryID], [TypeID]) ) as UnPivotData
     where RecID % 2 = 0) AS Old
INNER JOIN
    (select ActivityID, ActHistID, Field, FieldValue, RecID
     from cte
     UNPIVOT ( FieldValue for Field IN ([StartTime], [EndTime], [CategoryID], [TypeID]) ) as UnPivotData
     where RecID % 2 = 1) AS New
ON Old.ActivityID = New.ActivityID
    AND Old.RecID = (New.RecID - 1)
    AND Old.Field = New.Field
UNION
SELECT Old.RecId, (CASE Old.Field
                WHEN 'StartTime' THEN 1
                WHEN 'EndTime' THEN 2
                WHEN 'CategoryID' THEN 3
                WHEN 'TypeID' THEN 4 END) FieldDisplaySequence, Old.ActivityID, 1 , Old.Field, Old.FieldValue OldValue, New.FieldValue NewValue
FROM
    (select ActivityID, ActHistID, Field, FieldValue, RecID
     from cte
     UNPIVOT ( FieldValue for Field IN ([StartTime], [EndTime], [CategoryID], [TypeID]) ) as UnPivotData
     where RecID % 2 = 1) AS Old
INNER JOIN
    (select ActivityID, ActHistID, Field, FieldValue, RecID
     from cte
     UNPIVOT ( FieldValue for Field IN ([StartTime], [EndTime], [CategoryID], [TypeID]) ) as UnPivotData
     where RecID % 2 = 0) AS New
ON Old.ActivityID = New.ActivityID
    AND Old.RecID = (New.RecID - 1)
    AND Old.Field = New.Field
) FinalOutput
Order by RecId, FieldDisplaySequence

Cheers,
Pooja Falor

SQLScott · September 27th, 2007, 08:53 AM

I have an extra copy of the book, so for all your help, I'll be more than happy to send it to you.
Email me your address at [email protected] and i'll send it to you.
I've never sent anything to Sweden so hopefully this shouldn't be a problem...

Thanks for all your help.

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

SQLScott · September 27th, 2007, 10:35 AM

Peso and Pooja,

Thank you both for your assistance. Both are working beautifully (I modified them a bit to fit our exact enviroment) but they are both returning the information exactly as I am looking for. I had tried using the UNPIVOT operator earlier but could not get it working correctly, but Pooja's code was what I was looking for.

I will be experimenting with both to see if one performs better than the other. I really don't think one will stand out over the other simply becuase of the amount of records we will be working with.

Again, many thanks.

Scott

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Peso · September 28th, 2007, 11:01 AM

Here is the code a little more slimlined

Code:

-- Prepare sample data
DECLARE @Sample TABLE (ActHistID INT , ActivityID INT, StartTime DATETIME, EndTime DATETIME, ActivityCategoryID INT, ActivityTypeID INT)

INSERT  @Sample
SELECT  1, 163, '2007-09-24 23:00', '2007-09-24 00:00', 10006, 10022 UNION ALL
SELECT  3, 163, '2007-09-24 01:00', '2007-09-24 02:00', 10007, 10023 UNION ALL
SELECT  4, 163, '2007-09-24 02:00', '2007-09-24 03:00', 10008, 10024 UNION ALL
SELECT  5, 163, '2007-09-24 03:00', '2007-09-24 04:00', 10009, 10025

-- Stage the number of samples
DECLARE    @Items INT

SELECT    @Items = COUNT(*)
FROM    @Sample

-- Show the expected output
SELECT          y1.theColumn AS Field,
                y1.theValue AS OldValue,
                y2.theValue AS NewValue
FROM            (
            SELECT    ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) - 1 AS theOrder,
                p.theColumn,
                p.theValue
            FROM    (
                    SELECT    CONVERT(VARCHAR(20), CONVERT(CHAR(5), StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2)) AS StartTime,
                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2)) AS EndTime, 
                        CONVERT(VARCHAR(20), ActivityCategoryID) AS ActivityCategoryID,
                        CONVERT(VARCHAR(20), ActivityTypeID) AS ActivityTypeID
                    FROM    @Sample
                ) y
            UNPIVOT    (
                        theValue
                    FOR    theColumn IN (y.StartTime, y.EndTime, y.ActivityCategoryID, y.ActivityTypeID)
                ) AS p
        ) AS y1
INNER JOIN      (
            SELECT    ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) - 1 AS theOrder,
                p.theColumn,
                p.theValue
            FROM    (
                    SELECT    CONVERT(VARCHAR(20), CONVERT(CHAR(5), StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2)) AS StartTime,
                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2)) AS EndTime, 
                        CONVERT(VARCHAR(20), ActivityCategoryID) AS ActivityCategoryID,
                        CONVERT(VARCHAR(20), ActivityTypeID) AS ActivityTypeID
                    FROM    @Sample
                ) y
            UNPIVOT    (
                        theValue
                    FOR    theColumn IN (y.StartTime, y.EndTime, y.ActivityCategoryID, y.ActivityTypeID)
                ) AS p
        ) AS y2 ON y2.theColumn = y1.theColumn
            AND y2.theOrder / @Items = y1.theOrder / @Items + 1
ORDER BY        y1.theOrder / @Items,
        y1.theOrder