UNPIVOT Function Help
-
I have a user table with multiple column groups that I need to unpivot.
Heres the sample source table layout
SerialNumber
AccountName
Op0Name
Op1Name
Op2Name
Op3Name
Op0Date
Op1Date
Op2Date
Op3Date
Op0Value
Op1Value
Op2Value
Op3Value
I basically want to unpivot these into a single table in the following format
SerialNumber
AccountName
OpName
OpDate
OpValue
Anyone have ideas on how to do this in a single UNPIVOT function? I want to make sure that I can associate the right OpValue and OpDate with their respective OpName and I'm having a tough time. I can UNPIVOT any single group of them just fine with the following code..
select acctname, SerialNumber
, Opportunity_Name
FROM (SELECT AcctName, SerialNumber, op0Name, op1Name, op2Name, op3Name
FROM dbo.saleslinkAccts) s
UNPIVOT
(Opportunity_Name FOR Opportunity_Nbr IN (op0Name, op1Name, op2Name, op3Name
)) AS unpvt
Any help would be greatly appreciated.
Respectfully,
Michael Shugarman
-
I don't know of any unpivot function in 2005.
However this works in any version :
--Insert into normalized table...
Select SerialNumber, AccountName, Op0Name AS OPName, Op0Date AS OPDate, Op0Value AS OPValue FROM dbo.BaseTable
UNION ALL
Select SerialNumber, AccountName, Op1Name AS OPName, Op1Date AS OPDate, Op1Value AS OPValue FROM dbo.BaseTable
UNION ALL
Select SerialNumber, AccountName, Op2Name AS OPName, Op2Date AS OPDate, Op2Value AS OPValue FROM dbo.BaseTable
...
-
Michael,
GOOD QUESTION!!! It would be nice if you could perform an UNPIVOT on multiple columns at the same time. I don't think you can, but the code below seems to get the results you need. The key to this is using the ROW_NUMBER() function.
SELECT
baseTable.accountname
,baseTable.SerialNumber
,baseTable.Opportunity_Name
,optDates.Opportunity_Date
,optValues.Opportunity_Value
FROM
-- perform the first unpivot assigning a rownumber which will be used to join on
(SELECT
accountname, SerialNumber, Opportunity_Name
,row_number()
OVER
(PARTITION BY accountName, SerialNumber
ORDER BY accountName, SerialNumber) AS rn
FROM
(SELECT accountname, SerialNumber, op0Name, op1Name, op2Name, op3Name
FROM dbo.saleslinkAccts) s
UNPIVOT
(Opportunity_Name FOR Opportunity_Nbr IN (op0Name, op1Name, op2Name, op3Name)
) AS unpvt
) AS baseTable
-- perform another unpivot getting the values
LEFT JOIN
(SELECT
accountname, SerialNumber, Opportunity_Value
,row_number()
OVER
(PARTITION BY accountName, SerialNumber
ORDER BY accountName, SerialNumber) AS rn
FROM
(SELECT accountname, SerialNumber, op0Value, op1Value, op2Value, op3Value
FROM dbo.saleslinkAccts) v
UNPIVOT
(Opportunity_Value FOR opValue IN (op0Value, op1Value, op2Value, op3Value)
) AS unpvt) AS optValues
ON optValues.SerialNumber = baseTable.SerialNumber
AND optValues.accountName = baseTable.accountname
AND optValues.rn = baseTable.rn
-- now get the dates
LEFT JOIN
(SELECT
accountname, SerialNumber, Opportunity_Date
,row_number()
OVER
(PARTITION BY accountName, SerialNumber
ORDER BY accountName, SerialNumber) AS rn
FROM
(SELECT accountname, SerialNumber, op0Date, op1Date, op2Date, op3Date
FROM dbo.saleslinkAccts) v
UNPIVOT
(Opportunity_Date FOR opDate IN (op0Date, op1Date, op2Date, op3Date)
) AS unpvt) AS optDates
ON optDates.SerialNumber = baseTable.SerialNumber
AND optDates.accountName = baseTable.accountname
AND optDates.rn = baseTable.rn
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
From ss2k5 help:
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
Note: When PIVOT and UNPIVOT are used against databases that are upgraded to Microsoft SQL Server 2005, the compatibility level of the database must be set to 90. For information about how to set the database compatibility level, see sp_dbcmptlevel (Transact-SQL). -
The code above worked for me. ?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Jason,
You are a genius !
I was searching a solution about a multiple-column UNPIVOT for weeks however my problem was a little different because my source is a flat file.
Can I do in the same way or is it necessary tu use VB ?
( I know VB even less than SQL )
Thank you if you can help me, I am initial in SSIS ans SQL Server 2 k5.
BEGINNING
12/11/2009;03-V9-EXPLOIT-MAG-BDB;00:01:00;0;0;0;0;0;0;0;0;SGSI2KSIFAPP3;initialized;3484;0;0;0;0;SGSI2KSIFAPP4;initialized;2848;0;0;0;0;SGSI2KSIFAPP5;initialized;4292;0;0;0;0;SGSI2KSIFAPP6;initialized;3296
wished ARRIVAL
12/11/2009;03-V9-EXPLOIT-MAG-BDB;00:01:00;0;0;0;0;”ALL”;NULL;NULL;12/11/2009;03-V9-EXPLOIT-MAG-BDB;00:01:00;0;0;0;0;SGSI2KSIFAPP3;initialized;3484;
12/11/2009;03-V9-EXPLOIT-MAG-BDB;00:01:00;0;0;0;0;SGSI2KSIFAPP4;initialized;2848;
12/11/2009;03-V9-EXPLOIT-MAG-BDB;00:01:00;0;0;0;0;SGSI2KSIFAPP5;initialized;4292;
12/11/2009;03-V9-EXPLOIT-MAG-BDB;00:01:00;0;0;0;0;SGSI2KSIFAPP6;initialized;3296;
P.S.: Excuse-me if my english is not very good !
Note: Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply