Unpivot Multiple Columns

Question

Unpivot Multiple Columns

sqlpadawan_1

SSCommitted

Points: 1571
More actions
May 3, 2013 at 12:13 pm

#277348

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlpadawan_1 SSCommitted Points: 1571 More actions · Answer 2

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.