Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 28th, 2007, 11:13 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

And with common table expression
Code:

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 DECLARE @Items INT SELECT @Items = COUNT(*) FROM @Sample ;WITH Yak (theOrder, theColumn, theValue) AS (     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 ) -- Show the expected output SELECT y1.theColumn AS Field,                 y1.theValue AS OldValue,                 y2.theValue AS NewValue FROM Yak AS y1 INNER JOIN Yak AS y2 ON y2.theColumn = y1.theColumn WHERE y2.theOrder / @Items = y1.theOrder / @Items + 1 ORDER BY y1.theOrder / @Items,         y1.theOrder


 
Old October 1st, 2007, 05:52 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is my best effort yet

-- 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

-- Show the expected output
SELECT p.theColumn AS Field,
                MAX(CASE p.thePosition WHEN 0 THEN p.theValue END) AS OldValue,
                MAX(CASE p.thePosition WHEN 1 THEN p.theValue END) AS NewValue
FROM (
                        SELECT ROW_NUMBER() OVER (ORDER BY s.ActHistID) / 2 AS theSection,
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.StartTime, 100), 2)) AS StartTime,
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.EndTime, 100), 2)) AS EndTime,
                                        CONVERT(VARCHAR(20), s.ActivityCategoryID) AS ActivityCategoryID,
                                        CONVERT(VARCHAR(20), s.ActivityTypeID) AS ActivityTypeID,
                                        d.Delta AS thePosition
                        FROM @Sample AS s
                        CROSS JOIN (
                                                SELECT 1 AS Delta UNION ALL
                                                SELECT 0
                                        ) AS d
                ) AS y
UNPIVOT (
                                theValue
                        FOR theColumn IN (y.StartTime, y.EndTime, y.ActivityCategoryID, y.ActivityTypeID)
                ) AS p
GROUP BY p.theSection,
                p.theColumn
HAVING COUNT(*) > 1
ORDER BY p.theSection,
                CASE theColumn
                    WHEN 'StartTime' THEN 0
                    WHEN 'EndTime' THEN 1
                    WHEN 'ActivityCategoryID' THEN 2
                    ELSE 3
                END
 
Old October 2nd, 2007, 02:24 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For what is worth, this is a SQL Server 2000 approach
Code:
SELECT        d.theColumn AS Field,
        MAX(CASE WHEN c.FromID = d.ActHistID THEN theValue END) AS OldValue,
        MAX(CASE WHEN c.ToID = d.ActHistID THEN theValue END) AS NewValue
FROM        (
            SELECT        s1.ActHistID AS FromID,
                    MIN(s2.ActHistID) AS ToID
            FROM        tSample AS s1
            INNER JOIN    tSample AS s2 ON s2.ActHistID > s1.ActHistID
            GROUP BY    s1.ActHistID
        ) AS c
INNER JOIN    (
            SELECT        ActHistID,
                    'StartTime' AS theColumn,
                    CONVERT(CHAR(5), StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2) AS theValue,
                    1 AS thePosition
            FROM        tSample

            UNION ALL

            SELECT        ActHistID,
                    'EndTime' AS theColumn,
                    CONVERT(CHAR(5), EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), EndTime, 100), 2) AS theValue,
                    2 AS thePosition
            FROM        tSample

            UNION ALL

            SELECT        ActHistID,
                    'ActivityCategoryID' AS theColumn,
                    CONVERT(CHAR(20), ActivityCategoryID) AS theValue,
                    3 AS thePosition
            FROM        tSample

            UNION ALL

            SELECT        ActHistID,
                    'ActivityTypeID' AS theColumn,
                    CONVERT(CHAR(20), ActivityTypeID) AS theValue,
                    4 AS thePosition
            FROM        tSample
        ) AS d ON d.ActHistID IN (c.FromID, c.ToID)
GROUP BY    c.FromID,
        d.theColumn,
        d.thePosition
ORDER BY    c.FromID,
        d.thePosition
 
Old October 2nd, 2007, 03:08 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Better hack for SQL Server 2005
Code:
-- Show the expected output
SELECT          SUBSTRING(p.theColumn, 2, 20) AS Field,
                MAX(CASE p.thePosition WHEN 0 THEN p.theValue END) AS OldValue,
                MAX(CASE p.thePosition WHEN 1 THEN p.theValue END) AS NewValue
FROM            (
                        SELECT          ROW_NUMBER() OVER (ORDER BY s.ActHistID) / 2 AS theSection,
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.StartTime, 100), 2)) AS [1StartTime],
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.EndTime, 100), 2)) AS [2EndTime], 
                                        CONVERT(VARCHAR(20), s.ActivityCategoryID) AS [3ActivityCategoryID],
                                        CONVERT(VARCHAR(20), s.ActivityTypeID) AS [4ActivityTypeID],
                                        d.Delta AS thePosition
                        FROM            tSample AS s
                        CROSS JOIN      (
                                                SELECT    1 AS Delta UNION ALL
                                                SELECT    0
                                        ) AS d
                ) AS y
UNPIVOT         ( 
                                theValue
                        FOR     theColumn IN (y.[1StartTime], y.[2EndTime], y.[3ActivityCategoryID], y.[4ActivityTypeID])
                ) AS p
GROUP BY        p.theSection,
                p.theColumn
HAVING          COUNT(*) > 1
ORDER BY        p.theSection,
        p.theColumn











Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.