CODEWITHSUNDEEP
sqlsql-servert-sql
Kishore
Kishore

Reputation: 41

Split one row into multiple rows base on column name

I want to split one row into multiple rows in SQL Server, based on column names. Also want to achieve this without using union multiple times.

Below is the sample input & output. Also added SQL code for input table:

Click here for sample result

create table T (
   Id INT, 
   A_MIN INT, 
   A_MAX INT, 
   A_VAL INT, 
   B_MIN INT, 
   B_MAX INT, 
   B_VAL INT, 
   C_MIN INT, 
   C_MAX INT, 
   C_VAL INT
)

insert into T values (4334, 25, 40, 30, 1, 9, 7, 15, 28, 9)
insert into T values (4335, 45, 48, 46, 0, 0, 0, 3, 8, 1)

Upvotes: 1

Views: 1455

Answers (1)

Zhorov
Zhorov

Reputation: 29943

Unpivot the table using VALUES table value constructor and APPLY operator:

SELECT Id, a.*
FROM T
CROSS APPLY (VALUES
   (A_MIN, A_MAX, A_VAL, 'A'),
   (B_MIN, B_MAX, B_VAL, 'B'),
   (C_MIN, C_MAX, C_VAL, 'C')
) a ([Min], [Max], [Val], [Type])

Upvotes: 7

Related Questions