CODEWITHSUNDEEP
sql-server-2008ssisbidsunpivot
LegalEagle
LegalEagle

Reputation: 117

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!

Upvotes: 0

Views: 816

Answers (2)

GarethD
GarethD

Reputation: 69809

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);

Upvotes: 1

Joe C
Joe C

Reputation: 3993

I have found that for some people UnPivot is more confusing to learn than it is worth so I have always avoided it when possible. If you want to take a different approach, you can do this:

Load the data into a temp table and run the following query against it:

Select * From
    (
    Select AccountID, 'FreeText1' Title, FreeText1 TheData From TableA
    Union All
    Select AccountID, 'FreeText2' Title, FreeText2 TheData From TableA
    Union All
    Select AccountID, 'FreeText3' Title, FreeText3 TheData From TableA
    Union All
    Select AccountID, 'FreeText4' Title, FreeText4 TheData From TableA
    ) A
Order By AccountID, Title

Upvotes: 0

Related Questions