CODEWITHSUNDEEP
sqlsql-servert-sqlssisbids
kyooryu
kyooryu

Reputation: 1509

Pivoting columns based on a key

I have the following table:

key_column INT,
column_type_id INT, --has only 3 values, e.g. 1,2,3
value INT

I want to transfer the datat from that table into my DW fact table which looks like this:

key_column INT,
column_type_id INT,
column_type_1 INT,
column_type_2 INT,
column_type_3 INT

The matching criteria are as follows:

1) match columns using key_column 2) use column_type_id to determine to which column value should be inserted (if column_type_id = 1 then insert value to column_type_1, etc. after that discard column_type_id and don't make duplicate rows so key_column is distinct) .

Does anyone have any idea how to do it by either TSQL or (preferably) SSIS? I should also mention sorting for either merge or duplicate elimination is not an option, because my hardware is too weak to handle sorting of the amount of data I have.

Upvotes: 1

Views: 69

Answers (1)

John Woo
John Woo

Reputation: 263893

you can execute INSERT INTO...SELECT to insert the pivoted data from your original table,

INSERT INTO DWFACT( key_column, 
                    column_type_id, 
                    column_type_1, 
                    column_type_2, 
                    column_type_3
                    )
SELECT  key_column, ColTypeID, [1], [2], [3]
FROM    
        (
            SELECT  key_column, 
                    column_type_id as ColTypeID, 
                    column_type_id, 
                    value
            FROM    TableName
        ) org
        PIVOT
        (
            MAX(value)
            FOR column_type_id IN ([1], [2], [3])
        ) pvt
ORDER   BY key_column

Upvotes: 1

Related Questions