How to change the output with a dynamic T-SQL pivot table

Question

This is my table.

CREATE TABLE tpivot
    ([col1] varchar(80), [col2] varchar(80), [col3] varchar(80), [col4] varchar(80), [col5] varchar(80))
;

INSERT INTO tpivot
    ([col1], [col2], [col3], [col4], [col5])
VALUES
    ('Datum', 'EC', 'Mastercard', 'Postfinance', 'VISA'),
    ('01.12.2014', '-204.9', '-88', '0', '-19'),
    ('02.12.2014', '-352.9', '0', '79.9', '-20'),
    ('03.12.2014', '-105', '-182', '0', '-436'),
    ('04.12.2014', '-371', '-122,9', '-751', '-343')

;

My goal is to turn the table so that the output look like this.

col1         col2         col3        col4        col5
Datum        01.12.2014   02.12.2014  03.12.2014  04.12.2014
EC           -204.9       -352.9      -105        -371
Mastercard  -88           0           -182        -112.9
Postfinance  0            -79.9        0          -751
VISA         -19          -20         -436        -346

I need to have a dynamic pivot since i import the table from a csv that could have different amount of columns each time.

Can someone help me? I can't even get a static pivot to work :(

Thanks

Answer 1

Here's my solution with dynamic SQL.

IF OBJECT_ID('tPivot') IS NOT NULl
    DROP TABLE tpivot;

CREATE TABLE tpivot
    (ID INT IDENTITY(1,1), [col1] varchar(100), [col2] varchar(100), [col3] varchar(100), [col4] varchar(100), [col5] varchar(100));
INSERT INTO tpivot
    ([col1], [col2], [col3], [col4], [col5])
VALUES
    ('Datum', 'EC', 'Mastercard', 'Postfinance', 'VISA'),
    ('01.12.2014', '-204.9', '-88', '0', '-19'),
    ('02.12.2014', '-352.9', '0', '79.9', '-20'),
    ('03.12.2014', '-105', '-182', '0', '-436'),
    ('04.12.2014', '-371', '-122,9', '-751', '-343');

DECLARE @ColList VARCHAR(MAX),
        @IdList VARCHAR(MAX);

SELECT  @ColList = COALESCE(@ColList + ',','') + QUOTENAME(A.COLUMN_NAME),
        @IdList = COALESCE(@IdList + ',','') + QUOTENAME(ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION))
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE   TABLE_NAME = 'tPivot'
        AND COLUMN_NAME ! ='ID'

--SELECT @ColList
--SELECT @IdList


EXEC (
'WITH CTE
AS
(
    SELECT *
    FROM
    (
        SELECT  ID,
                Column_Name,
                ROW_NUMBER() OVER (PARTITION BY ID ORDER BY col) row_num,
                val
        FROM tPivot A
        INNER JOIN (SELECT Column_Name AS COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''tPivot'' ) B
        ON A.ID = B.ORDINAL_POSITION - 1
        UNPIVOT
        (
            val for col in (' + @colList + ')
        ) unpvt
    ) A
    PIVOT
    (
        MAX(val) FOR column_name IN (' + @colList + ')
    ) pvt
)

SELECT *
FROM
(
    SELECT ID,
            row_num,
            coalesce(' + @colList + ') val
    FROM cte
) A
PIVOT
(
    MAX(val) FOR ID IN (' + @IdList + ')
) pvt'
)