• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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?
2 Solutions
Scott PletcherSenior DBACommented:
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.
Mike EghtebasDatabase and Application DeveloperCommented:

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ãoMSSQL Senior EngineerCommented:
mikegrad7, do you still need help with this question?
mikegrad7Author Commented:
thanks to everyone for you help, i was able to get this working as i needed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now