CODEWITHSUNDEEP
sqlsql-serversql-server-2008
TigermanSD
TigermanSD

Reputation: 77

Unpivot Multiple Columns in into Rows

I did some searching and didn't come up with a good answer. I am trying to create a table from data and then Unpivot it in SQL Server, but can't get the columns and rows required.

IF OBJECT_ID('tempdb..##EXPECT_PERCENTS') IS NOT NULL
   DROP TABLE ##EXPECT_PERCENTS

CREATE TABLE ##EXPECT_PERCENTS (
   PAYOR_ID VARCHAR(40)
   ,TEST_TYPE VARCHAR(40)
   ,[2011-08-31] INT
   ,[2011-09-30] INT
)
GO

INSERT INTO ##EXPECT_PERCENTS VALUES('UHC','UDT','1','2');

select * from ##EXPECT_PERCENTS

SELECT PAYOR_ID, TEST_TYPE, EXPECT
FROM ##EXPECT_PERCENTS
UNPIVOT 
(
  EXPECT FOR EXPECTS IN ([2011-08-31],[2011-09-30])
) AS u

I am trying to unpivot the dates as well so that there is another field called "Date" with the two dates representing each number. The first line should be:

UHC UDT 1 2011-08-31

Upvotes: 0

Views: 979

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Needing to unpivot multiple columns can often be easier without the unpivot command. One option is to use CROSS APPLY:

select payor_id, test_type, expect, expectdate
from expect_percents
cross apply
(
  select [2011-08-31],'2011-08-31' union all
  select [2011-09-30],'2011-09-30'
) c (expect, expectdate);

Another option is to use UNION ALL:

select payor_id, test_type, [2011-08-31] expect, '2011-08-31' date
from expect_percents
union all
select payor_id, test_type, [2011-09-30] expect, '2011-09-30' date
from expect_percents;

Upvotes: 2

Related Questions