Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unpivot Multiple Columns Expand / Collapse
Author
Message
Posted Friday, May 03, 2013 12:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:13 AM
Points: 92, Visits: 632
This is my first shot at an trying to unpivot multiple columns, and I'm not getting any results.

I have a table that has an id, and then 20 columns, 10 values and 10 exp dates. I want to unpivot it and create a table with rows of 3 columns each row (pk_value, date, exp value).

here's the code to recreate the unpivot. Unpivoting by one column works fine, when I try to unpivot by the 2nd column and then include the where clause, i don't get any records returned.

USE tempdb
GO

CREATE TABLE [dbo].[ERFiles](
[pk_value] [int] NOT NULL,
[ER_1] [decimal](14, 7) NULL,
[ER_1_ExpDate] [date] NULL,
[ER_2] [decimal](14, 7) NULL,
[ER_2_ExpDate] [date] NULL,
[ER_3] [decimal](14, 7) NULL,
[ER_3_ExpDate] [date] NULL,
[ER_4] [decimal](14, 7) NULL,
[ER_4_ExpDate] [date] NULL,
[ER_5] [decimal](14, 7) NULL,
[ER_5_ExpDate] [date] NULL,
[ER_6] [decimal](14, 7) NULL,
[ER_6_ExpDate] [date] NULL,
[ER_7] [decimal](14, 7) NULL,
[ER_7_ExpDate] [date] NULL,
[ER_8] [decimal](14, 7) NULL,
[ER_8_ExpDate] [date] NULL,
[ER_9] [decimal](14, 7) NULL,
[ER_9_ExpDate] [date] NULL,
[ER_10] [decimal](14, 7) NULL,
[ER_10_ExpDate] [date] NULL,
)

INSERT INTO [dbo].[ERFiles]([pk_value], [ER_1], [ER_1_ExpDate], [ER_2], [ER_2_ExpDate], [ER_3], [ER_3_ExpDate], [ER_4], [ER_4_ExpDate], [ER_5], [ER_5_ExpDate], [ER_6], [ER_6_ExpDate], [ER_7], [ER_7_ExpDate], [ER_8], [ER_8_ExpDate], [ER_9], [ER_9_ExpDate], [ER_10], [ER_10_ExpDate])
SELECT 1, 40.3399000, '20051231 00:00:00.000', 40.3399000, '20101231 00:00:00.000', 40.3399000, '20111231 00:00:00.000', 40.3399000, '20121231 00:00:00.000', 40.3399000, '20131231 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 2, 18.3705000, '19950930 00:00:00.000', 18.3705000, '19951231 00:00:00.000', 18.3705000, '20010101 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 3, 1.2355000, '20120930 00:00:00.000', 1.2672000, '20121031 00:00:00.000', 1.2807000, '20121130 00:00:00.000', 1.2774000, '20121231 00:00:00.000', 1.3126000, '20130131 00:00:00.000', 1.3222000, '20130228 00:00:00.000', 1.3567000, '20130331 00:00:00.000', 1.3271000, '20130430 00:00:00.000', 1.3469000, '20130531 00:00:00.000', 0.0000000, NULL UNION ALL
SELECT 4, 1.2041000, '20121231 00:00:00.000', 1.2096000, '20130131 00:00:00.000', 1.2385000, '20130228 00:00:00.000', 1.2347000, '20130331 00:00:00.000', 1.2222000, '20130430 00:00:00.000', 1.2149000, '20130531 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 5, 1.3440000, '20090831 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 6, 1.9558300, '20051231 00:00:00.000', 1.9558300, '20101231 00:00:00.000', 1.9558300, '20111231 00:00:00.000', 1.9558300, '20121231 00:00:00.000', 1.9558300, '20131231 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 7, 0.8149900, '19950930 00:00:00.000', 0.8149900, '19951231 00:00:00.000', 0.8149900, '20010101 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 8, 7.4574000, '20121231 00:00:00.000', 7.4608000, '20130131 00:00:00.000', 7.4629000, '20130228 00:00:00.000', 7.4604000, '20130331 00:00:00.000', 7.4532000, '20130430 00:00:00.000', 7.4562000, '20130531 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 9, 3.5310730, '19950930 00:00:00.000', 3.5310730, '19951231 00:00:00.000', 3.5310730, '20010101 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALL
SELECT 10, 0.1400000, '20100630 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL

SELECT pk_value
,ERValue
,ERExpDate
FROM ( SELECT [pk_value]
,[ER_1]
,[ER_1_ExpDate]
,[ER_2]
,[ER_2_ExpDate]
,[ER_3]
,[ER_3_ExpDate]
,[ER_4]
,[ER_4_ExpDate]
,[ER_5]
,[ER_5_ExpDate]
,[ER_6]
,[ER_6_ExpDate]
,[ER_7]
,[ER_7_ExpDate]
,[ER_8]
,[ER_8_ExpDate]
,[ER_9]
,[ER_9_ExpDate]
,[ER_10]
,[ER_10_ExpDate]
FROM [dbo].[ERFiles]
) M UNPIVOT ( ERExpDate FOR ERExpDates IN ( ER_1_ExpDate, ER_2_ExpDate,
ER_3_ExpDate, ER_4_ExpDate,
ER_5_ExpDate, ER_6_ExpDate,
ER_7_ExpDate, ER_8_ExpDate,
ER_9_ExpDate,
ER_10_ExpDate ) ) U1
UNPIVOT ( ERValue FOR ERValues IN ( ER_1, ER_2, ER_3, ER_4, ER_5, ER_6,
ER_7, ER_8, ER_9, ER_10 ) ) U2
WHERE RIGHT(ERExpDates, 1) = RIGHT(ERValues, 1);



It's probably something simple, but I not seeing it. Any ideas?

Thanks
Post #1449319
Posted Friday, May 03, 2013 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:01 PM
Points: 12,744, Visits: 31,074
great job providing the same DDL and Data!

maybe i'm missing the hard part of the question and overlooking the obvious,it, but does this do what you want? a simple union, ten times for each value/date pair in the table?
SELECT [pk_value], ER_1,ER_1_ExpDate  FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_2,ER_2_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_3,ER_3_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_4,ER_4_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_5,ER_5_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_6,ER_6_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_7,ER_7_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_8,ER_8_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_9,ER_9_ExpDate FROM [dbo].[ERFiles] UNION ALL
SELECT [pk_value], ER_10,ER_10_ExpDate FROM [dbo].[ERFiles]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449323
Posted Tuesday, May 07, 2013 7:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:13 AM
Points: 92, Visits: 632
Thanks for the response. I guess I thought it would be cool to use an unpivot, but the union works and after reading some additional posts on unpivot, maybe it's not as cool as it sounds.
Post #1450130
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse