Monday, May 28, 2012

Pivot and Unpivot in Sql server


CREATE TABLE #tblMergeOutput
(
    ID                                BIGINT IDENTITY(1,1)
    , MergeAction                    NVARCHAR(100)
    , Source_barclay_id                BIGINT NULL
    , Source_name                    VARCHAR(200) NULL
    , Source_Address                VARCHAR(200) NULL
    , Target_barclay_id                BIGINT NULL
    , Target_name                    VARCHAR(200) NULL
    , Target_Address                VARCHAR(200) NULL
)

INSERT INTO #tblMergeOutput VALUES ('UPDATE', 1, 'Name1', 'Address1', 1, 'Name11', 'Address11')
INSERT INTO #tblMergeOutput VALUES ('UPDATE', 2, 'Name2', 'Address2', 2, 'Name22', 'Address22')

SELECT * FROM #tblMergeOutput

SELECT
*
FROM
(
    SELECT
    *
    FROM
    (
        SELECT
        *
        FROM
        (
            SELECT
                ID
                , MergeAction
                , 'OldValue' AS RecordType
                , Source_barclay_id AS barclay_id
                , CAST( Source_name AS VARCHAR(MAX) ) AS Name
                , CAST( Source_Address AS VARCHAR(MAX) ) AS Address
            FROM #tblMergeOutput
           
            UNION ALL
           
            SELECT
                ID
                , MergeAction
                , 'NewValue' AS RecordType
                , Target_barclay_id AS barclay_id
                , CAST( Target_name AS VARCHAR(MAX) ) AS Name
                , CAST( Target_Address AS VARCHAR(MAX) ) AS Address
            FROM #tblMergeOutput
        ) AS Data
        UNPIVOT( [Value] For [FieldName] IN ( [Name], [Address] ) ) AS upvt
    ) AS Data
    PIVOT( MIN ( [Value]) FOR [RecordType] IN ([OldValue], [NewValue]) ) AS pvt
) AS Data
WHERE OldValue != NewValue

DROP TABLE #tblMergeOutput

No comments:

Post a Comment