We help IT Professionals succeed at work.

SQL Server Transpose , partially at least

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?
Watch Question

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
SELECT ID1, ID2, ca.Property, ca.Value
FROM table_name
    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

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
    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

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

mikegrad7, do you still need help with this question?


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