Pivoting multiple columns with ordered names and attributes in sql server 2008 using the PIVOT function.

A friend of mine came across an issue where they needed to take data from a table, that had a label and a value, and then pivot it out into a single row and they were unsure of how to accomplish the goal.  So I created this little example to show them what needed to be done.

In the data they have a table of labeled attributes combined with a sort order.  The sort order is the priority of any individual attribute, and the attributes have labels, and for any foreign key has a value associated with that specific label.  

Key, AttributeName, Attribute Value, SortOrder

An example would be:

FKeyValue	SortOrder	ItemName	ItemValue
1	2	Item2	2
1	3	Item3	3
1	4	Item4	4
1	5	Item5	5
2	1	Item1	1
2	2	Item2	2
2	3	Item3	3
2	14	Item14	14
2	31	Item31	31
2	51	Item51	51

And they want the data to pivot to:

fkeyvalue	att1name	att1value	att2name	att2value	att3name	att3value	att4name	att4value	att5name	att5value
1	Item2	2	Item3	3	Item4	4	Item5	5	NULL	NULL
2	Item1	1	Item2	2	Item3	3	Item14	14	Item31	31

So how do you approach this problem?  Because SQL doesn't allow you to pivot multiple item data, you have to approach the problem in parts.  My solution is to pivot the data twice around the foreign key, and use a row number assigned at the beginning of the operations to manage the column names and column order the data is exported.

The idea is that you first create a table that has the order you want the data to be in, this gives you the attribute number, and aligns the data in the way you want it to appear in the columns.  The data after this initial sort step will look like:

FKeyValue	SortOrder	ItemName	ItemValue	row_num
1	2	Item2	2	1
1	3	Item3	3	2
1	4	Item4	4	3
1	5	Item5	5	4
2	1	Item1	1	1
2	2	Item2	2	2
2	3	Item3	3	3
2	14	Item14	14	4
2	31	Item31	31	5
2	51	Item51	51	6

Then you do two pivots, one pivot is to generate the name attribute columns, and the second pivot is to create the value attribute columns.  Then you join the columns back to their foreign key, and select the order you want the columns to be in.

So the first pivoted table data would look like:

FKeyValue	1	2	3	4	5
1	Item2	Item3	Item4	Item5	NULL
2	Item1	Item2	Item3	Item14	Item31

 And the second pivoted table will look like:

FKeyValue	1	2	3	4	5
1	2	3	4	5	NULL
2	1	2	3	14	31

And after you join both tables together, and do a bit of column renaming you get:

fkeyvalue	att1name	att1value	att2name	att2value	att3name	att3value	att4name	att4value	att5name	att5value
1	Item2	2	Item3	3	Item4	4	Item5	5	NULL	NULL
2	Item1	1	Item2	2	Item3	3	Item14	14	Item31	31

Below is a complete example, one done with temp tables, and one done with a CTE that also has a little bit of column renaming so you can see how to pull out appropriate column names dynamically using the row number from the data.

IF Object_id('tempdb..#tmp_multipivotexample') IS NOT NULL

  BEGIN

      DROP TABLE #tmp_multipivotexample

  END

/* First create some test data*/

CREATE TABLE #tmp_multipivotexample

  (

     fkeyvalue INT NOT NULL,

     sortorder INT,

     itemname  VARCHAR(255),

     itemvalue VARCHAR(255)

  )

INSERT INTO #tmp_multipivotexample

            (fkeyvalue,

             sortorder,

             itemname,

             itemvalue)

SELECT 1       AS KeyValue,

       2       AS SortOrder,

       'Item2' AS ItemName,

       '2'     AS ItemValue

UNION

SELECT 1       AS FKeyValue,

       4       AS SortOrder,

       'Item4' AS ItemName,

       '4'     AS ItemValue

UNION

SELECT 1       AS FKeyValue,

       5       AS SortOrder,

       'Item5' AS ItemName,

       '5'     AS ItemValue

UNION

SELECT 1       AS FKeyValue,

       3       AS SortOrder,

       'Item3' AS ItemName,

       '3'     AS ItemValue

UNION

SELECT 2       AS FKeyValue,

       1       AS SortOrder,

       'Item1' AS ItemName,

       '1'     AS ItemValue

UNION

SELECT 2       AS KeyValue,

       3       AS SortOrder,

       'Item3' AS ItemName,

       '3'     AS ItemValue

UNION

SELECT 2        AS FKeyValue,

       14       AS SortOrder,

       'Item14' AS ItemName,

       '14'     AS ItemValue

UNION

SELECT 2        AS FKeyValue,

       51       AS SortOrder,

       'Item51' AS ItemName,

       '51'     AS ItemValue

UNION

SELECT 2        AS FKeyValue,

       31       AS SortOrder,

       'Item31' AS ItemName,

       '31'     AS ItemValue

UNION

SELECT 2       AS FKeyValue,

       2       AS SortOrder,

       'Item2' AS ItemName,

       '2'     AS ItemValue

--select * from #tmp_multipivotexample

/* Second Now that we have data, lets grab the data and make faux row numbers for the data for each key */

IF Object_id('tempdb..#InitialSortStep') IS NOT NULL

  BEGIN

      DROP TABLE #initialsortstep

  END

SELECT *,

       Row_number()

         OVER(

           partition BY fkeyvalue

           ORDER BY sortorder ASC) row_num

INTO   #initialsortstep

FROM   #tmp_multipivotexample

--select * from #InitialSortStep

/* Now that we have the initial sort step, we can see it sorts the data and properly assigned attribute numbers

so lets do the first pivot */

IF Object_id('tempdb..#PivotedItemNameByFKeyValue') IS NOT NULL

  BEGIN

      DROP TABLE #pivoteditemnamebyfkeyvalue

  END

SELECT *

INTO   #pivoteditemnamebyfkeyvalue

FROM   (SELECT fkeyvalue,

               itemname,

               row_num

        FROM   #initialsortstep) AS InitPivot

       PIVOT ( Max(itemname)

             FOR row_num IN ([1],

                             [2],

                             [3],

                             [4],

                             [5]) ) AS pivoted

--select * from #pivoteditemnamebyfkeyvalue

/*  Now to produce the properly sorted item value data */

IF Object_id('tempdb..#PivotedItemValuesByFKeyValue') IS NOT NULL

  BEGIN

      DROP TABLE #pivoteditemvaluesbyfkeyvalue

  END

SELECT *

INTO   #pivoteditemvaluesbyfkeyvalue

FROM   (SELECT fkeyvalue,

               itemvalue,

               row_num

        FROM   #initialsortstep) AS InitPivot

       PIVOT ( Max(itemvalue)

             FOR row_num IN ([1],

                             [2],

                             [3],

                             [4],

                             [5]) ) AS pivoted

--select * from #pivoteditemvaluesbyfkeyvalue

/* now to bring all of it together */

SELECT PINBFV.fkeyvalue,

       PINBFV.[1] AS att1name,

       PINBFN.[1] AS att1value,

       PINBFV.[2] AS att2name,

       PINBFN.[2] AS att2value,

       PINBFV.[3] AS att3name,

       PINBFN.[3] AS att3value,

       PINBFV.[4] AS att4name,

       PINBFN.[4] AS att4value,

       PINBFV.[5] AS att5name,

       PINBFN.[5] AS att5value

FROM   #pivoteditemnamebyfkeyvalue PINBFV

       JOIN #pivoteditemvaluesbyfkeyvalue PINBFN

         ON PINBFV.fkeyvalue = PINBFN.fkeyvalue

/*now as a CTE, in this CTE I actually use the row number function to do column naming as well, and re-designed the pivot

I think this makes it more readable. */

;

WITH cte_initialsortstep

     AS (SELECT *,

                Row_number()

                  OVER(

                    partition BY fkeyvalue

                    ORDER BY sortorder ASC) row_num

         FROM   #tmp_multipivotexample),

     cte_pivoteditemnamebyfkeyvalue

     AS (SELECT *

         FROM   (SELECT fkeyvalue,

                        itemname,

                        'att' + Cast(row_num AS VARCHAR(10)) + 'name' AS Name

                 FROM   cte_initialsortstep) AS InitPivot

                PIVOT ( Max(itemname)

                      FOR name IN ([att1name],

                                   [att2name],

                                   [att3name],

                                   [att4name],

                                   [att5name]) ) AS pivoted),

     pivoteditemvaluesbyfkeyvalue

     AS (SELECT *

         FROM   (SELECT fkeyvalue,

                        itemvalue,

                        'att' + Cast(row_num AS VARCHAR(10))

                        + 'value' AS Value

                 FROM   #initialsortstep) AS InitPivot

                PIVOT ( Max(itemvalue)

                      FOR value IN ([att1value],

                                    [att2value],

                                    [att3value],

                                    [att4value],

                                    [att5value]) ) AS pivoted)

SELECT PINBFV.fkeyvalue,

       PINBFV.att1name  AS att1name,

       PINBFN.att1value AS att1value,

       PINBFV.att2name  AS att2name,

       PINBFN.att2value AS att2value,

       PINBFV.att3name  AS att3name,

       PINBFN.att3value AS att3value,

       PINBFV.att4name  AS att4name,

       PINBFN.att4value AS att4value,

       PINBFV.att5name  AS att5name,

       PINBFN.att5value AS att5value

FROM   cte_pivoteditemnamebyfkeyvalue PINBFV

       JOIN pivoteditemvaluesbyfkeyvalue PINBFN

         ON PINBFV.fkeyvalue = PINBFN.fkeyvalue 

How to use a stored procedure as a SOURCE in Informatica

In SSIS there is an ability to use a stored procedure as the source for an OleDB object.  The example would be for a stored procedure that returns some rows with the columns a, b, and c to return those rows via a stored proc, and then do something with them, say write to a file.

So where I work there is a push to start converting things to Informatica, and the training revolving around Informatica is based mostly on doing the "work" of a stored procedure inside of Informatica, IE Joins/merges, Unions, with at best some embeded sql or the use of views.

But no training on how to do what is trivial, simple, and generally good practice in SSIS to pull data from a stored procedure and then manipulate the data into a file, or another destination.

After some fiddling around with the Informatica powercenter designer I figured out a method for this.

Step 1.  Create a dummy Microsoft Sql Server source (or whatever database source you are using) and add the columns with the column definitions that you want to your source.

Lets say your source is:

create proc dbo.PersonCompany

SELECT
FIRSTNAME

,LASTNAME

,COMPANYNAME

from person join companyperson on person.personid = companyperson.personid 

join company on company.companyid = companyperson.companyid

Though this could be in a view, lets say that this was a far more complicated procedure and it eventually returned these results. 

The source we would create would have these columns with the appropriate lengths.

Step 2.  Create a mapping and place the source in the mapping.  This will generate a source and a mapping to a source qualifier.

In the source qualifier you can then override the sql statement to be "exec dbo.PersonCompany" and now it will use the stored procedure as a source.

After discussing this with our internal Informatica expert, he did note that this would be "hidden" visually during debugging, since you cannot add notes to the mapping in Informatica, and it would be harder to debug, but you have a lot of legacy SSIS packages that are being converted over to Informatica, it would be easier to convert them in place if you company has been using stored procedures heavily to manipulate and present data.