CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012pivot
Chester John
Chester John

Reputation: 85

PIVOT and duplicate some columns of a table

I have a FormSummary table: 

formID    formName    Version    ReleaseDate    Location
========================================================
AA-01     PAYMENTS    1.3         1/1/2012      CA
AA-02     LOANS       1.4         2/1/2012      CA
AA-03     ADVANCES    1.6         3/20/2012     CA
AA-01     PAYMENTS    1.2         12/1/2011     US
AA-02     LOANS       1.4         2/1/2012      US
AA-03     ADVANCES    1.6         3/20/2012     US
AA-01     PAYMENTS    1.3         12/1/2012     PH
AA-02     LOANS       1.4         2/1/2012      PH
AA-03     ADVANCES    1.5         2/14/2012     PH

How can I pivot the location column so that I could come up with the following output: 

formID    formName    Version (CA)    ReleaseDate (CA)    Version (US)    ReleaseDate (US)    Version (PH)    ReleaseDate (PH)
==============================================================================================================================
AA-01     PAYMENTS    1.3             1/1/2012            1.2             12/1/2011           1.3             12/1/2012
AA-02     LOANS       1.4             2/1/2012            1.4             2/1/2012            1.4             2/1/2012
AA-03     ADVANCES    1.6             3/20/2012           1.6             3/20/2012           1.5             2/14/2012

Script to create table:

CREATE TABLE #FormSummary (
    formID nvarchar(5), 
    formName nvarchar(max), 
    [Version] float, 
    ReleaseDate date, 
    [Location] nvarchar(2)
)

INSERT INTO #FormSummary VALUES
('AA-01', 'PAYMENTS', 1.3, '1/1/2012', 'CA'),
('AA-02', 'LOANS', 1.4, '2/1/2012', 'CA'),
('AA-03', 'ADVANCES', 1.6, '3/20/2012', 'CA'),
('AA-01', 'PAYMENTS', 1.2, '12/1/2011', 'US'),
('AA-02', 'LOANS', 1.4, '2/1/2012', 'US'),
('AA-03', 'ADVANCES', 1.6, '3/20/2012', 'US'),
('AA-01', 'PAYMENTS', 1.3, '12/1/2012', 'PH'),
('AA-02', 'LOANS', 1.4, '2/1/2012', 'PH'),
('AA-03', 'ADVANCES', 1.5, '2/14/2012', 'PH')

Upvotes: 2

Views: 41

Answers (1)

gofr1
gofr1

Reputation: 15987

At first use sys.columns table to obtain column names. Then CROSS JOIN them with Locations to obtain column names like

,[Version (CA)],[ReleaseDate (CA)],[Version (PH)],[ReleaseDate (PH)],[Version (US)],[ReleaseDate (US)]

Then use UNPIVOT to make table look like this:

formID  formName    Columns             Values
AA-01   PAYMENTS    Version (CA)        1.3
AA-01   PAYMENTS    ReleaseDate (CA)    2012-01-01
AA-02   LOANS       Version (CA)        1.4
...
AA-03   ADVANCES    Version (PH)        1.5
AA-03   ADVANCES    ReleaseDate (PH)    2012-02-14

Then use PIVOT with column names stored in @col

This script will give you what you need:

DECLARE @sql nvarchar(max),
        @col nvarchar(max)

SELECT @col = (
    SELECT ',' + QUOTENAME([name]+ ' ('+[Location]+')')
    FROM sys.columns c
    CROSS JOIN (
        SELECT DISTINCT [Location] 
        FROM #FormSummary
        ) as f
    WHERE c.[object_id] = OBJECT_ID(N'#FormSummary') and c.column_id > 2 and [name] != 'Location'
    ORDER BY [Location]
    FOR XML PATH('')
)

SELECT @sql = N'
SELECT *
FROM (
    SELECT  formID, 
            formName,
            [Columns] + '' (''+[Location]+'')'' as [Columns],
            [Values]
    FROM (
        SELECT  formID, 
                formName, 
                CAST([Version] as nvarchar(max)) [Version], 
                CAST(ReleaseDate as nvarchar(max)) ReleaseDate, 
                CAST([Location] as nvarchar(max)) [Location]
        FROM #FormSummary
    ) as t
    UNPIVOT (
        [Values] FOR [Columns] IN ([Version], ReleaseDate)
    ) as unpvt
) as d
PIVOT (
    MAX([Values]) FOR [Columns] IN ('+STUFF(@col,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql

Output:

formID  formName    Version (CA)    ReleaseDate (CA)    Version (PH)    ReleaseDate (PH)    Version (US)    ReleaseDate (US)
AA-03   ADVANCES    1.6             2012-03-20          1.5             2012-02-14          1.6             2012-03-20
AA-02   LOANS       1.4             2012-02-01          1.4             2012-02-01          1.4             2012-02-01
AA-01   PAYMENTS    1.3             2012-01-01          1.3             2012-12-01          1.2             2011-12-01

Upvotes: 2

Related Questions