sql-server-2008ssisbidsunpivot

SSIS Unpivot including column names

(BIDS on SQL Server 2008)

I have a flat file (pipe-delimited) which I have successfully parsed to the following format:

AccountID    FreeText1    FreeText2    FreeText3    FreeText4
1            Some text    More text    Other text   Different Text
2            Some text    More text    Other text   Different Text
3            Some text    More text    Other text   Different Text

I need the end result to look like this:

AccountID    Title      TheData
1            FreeText1  Some text
1            FreeText2  More text
1            FreeText3  Other text
1            FreeText4  Different Text
2            FreeText1  Some text
2            FreeText2  More text
2            Freetext3  Other text
2            FreeText4  Different Text
3            FreeText1  Some text
3            FreeText2  More text
3            FreeText3  Other text
3            FreeText4  Different Text

I am still rather new to SSIS so learning as I go. Everything I found on the Unpivot transformation seems to be what I need, but I haven't been able to figure out how to get it to Unpivot based on the NAME of the column ("FreeText1", etc), nor have I been able to fully grasp how to set up the Unpivot transform to even get close to the desired results.

I haven't yet found any SSIS formulas I could use in a Derived Column to get the column name programmatically, thinking maybe I could generate the column names in a Derived Column and then Merge Join the two together... but that doesn't seem like a very efficient method and I couldn't make it work anyway. I have tried setting up a Derived Column to return the column names in hard code (using "FreeText1" as a formula, for example), however I remain unsure as to how to combine this with the Unpivoted results.

Any input would be greatly appreciated!

Solution

  • You could use the UNPIVOT transformation, which should look something like

    enter image description here

    Or you could load the data to a staging table and use the TSQL UNPIVOT function:

    SELECT  upvt.AccountID, upvt.Title, upvt.TheData
FROM    dbo.StagingTable AS t
UNPIVOT (Title FOR TheData IN (FreeText1, FreeText2, FreeText3, FreeText4)) AS upvt;

    Or slightly longer winded, but more flexible is to use CROSS APPLY along with a table value constructor to unpivot data. e.g.

    SELECT  t.AccountID, upvt.Title, upvt.TheData
FROM    dbo.StagingTable AS t
        CROSS APPLY
        (VALUES
            ('FreeText1', FreeText1),
            ('FreeText2', FreeText2),
            ('FreeText3', FreeText3),
            ('FreeText4', FreeText4)
        ) AS upvt (Title, TheData);