SQL Server Transpose , partially at least

mikegrad7
mikegrad7 used Ask the Experts™
on
Hi, i am trying to create a SQL Sp that will output some data, but it needs to do a partial transpose.

the current data is in rows, and looks like this. Lets call this Table CURRDATA
ID 1             ID2           TYPE        RATE   END_DATE
ABC123      786efr      XXX         5.5       12/31/2019
XXYYZZ       AABBCC   YYY         6.9        6/30/2020

etc... they key for the table is the combination of ID1 and ID 2

I need to re-arrange this output for import to another system, and it needs to be somewhat transposed. The output should look like this, with a line for each value outside the key.
 
ID1               ID2                Property                    Value
ABC123      786efr            TYPE                           XXX
ABC123      786efr            RATE                           5.5
ABC123      786efr            END_DATE                 12/31/2019
XXYYZZ       AABBCC         TYPE                           YYY
XXYYZZ       AABBCC         RATE                           6.9
XXYYZZ       AABBCC          END_DATE                6/30/2020

Can someone help me write this SQL to get the desired output?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT ID1, ID2, ca.Property, ca.Value
FROM table_name
CROSS APPLY (
    VALUES('Type', TYPE),('Rate',RATE),('End_Date',END_DATE)
) AS ca(Property, Value)

--This syntax assumes at least SQL 2008.
--If you're on 2005, the CROSS APPLY will need the code adjusted, but the basic approach still works.
Database and Application Developer
Commented:
@Scott,

To learn from your good solution, I made #t to test it. I found that we need to do some conversion like:
SELECT ID1, ID2, ca.Property, ca.Value
FROM #t
CROSS APPLY (
    VALUES('Type', TYPE),('Rate',cast(RATE as varchar(10))),('End_Date',cast(END_DATE as varchar(10)))
) AS ca(Property, Value)

Open in new window


temp table:
create table #t(ID1 varchar(10), ID2 varchar(10), [TYPE] varchar(10), RATE decimal(6,2), END_DATE date);
insert into #t(ID1, ID2, [TYPE], RATE, END_DATE) values
('ABC123', '786efr', 'XXX', 5.5, '12/31/2019')
, ('XXYYZZ', 'AABBCC', 'YYY', 6.9, '6/30/2020');

Open in new window


Mike
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
mikegrad7, do you still need help with this question?

Author

Commented:
thanks to everyone for you help, i was able to get this working as i needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial