CODEWITHSUNDEEP
sql-servert-sql
Doc
Doc

Reputation: 5266

Rotate SQL Server table

I have an ULTRALEGACY db where, for unknown reasons (to me), parameters tables are structured like this:

|Parameter1|Parameter2|Parameter3|Parameter4 ...
------------------------------------------------
|  Value1  |  Value2  |  Value3  |  Value4   ...

dozens and dozens of columns and a single values row (yeah, makes no sense for me either)

How can I query those tables for a result like

|Parameter|Value |
-----------------
|param1   |value1|
|param2   |value2|
|param3   |value3|
...

I just managed to get the first column with

SELECT COLUMN_NAME as Parameter
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='parameter_table_name'

but I can't figure out how to get value columns, I guess I have to pivot the table but I don't know how to do that

Upvotes: 0

Views: 353

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would like to use cross apply

select a.* from <table> t
cross apply(
            values ('param1', t.Parameter1),
                   ('param2', t.Parameter2),
                   ('param3', t.Parameter3),
                   ('param4', t.Parameter4)
         ) a(Parameter, Value)

Result :

Parameter   Value
param1      Value1
param2      Value2
param3      Value3
param4      Value4

Upvotes: 2

sepupic
sepupic

Reputation: 8687

What you need is UNPIVOT.

My example is for fixed number of columns, if it's variable you need to use dynamic sql to get the list of them and to construct UNPIVOT statement.

declare @t table (p1 int, p2 int, p3 int);
insert into @t values(1, 2, 3);

select param, val
from @t unpivot (val for param in (p1, p2, p3))u;

Upvotes: 3

Related Questions