CODEWITHSUNDEEP
sql-server
skyline01
skyline01

Reputation: 2101

re-arrange SQL table like an unpivot

I am working in SQL Server 2014. I have a table that I am trying to re-arrange. Let's call the table 'table1'. This table does not have a primary key column, but has a few columns that comprise the natural key. Let's call these natural key columns 'NK1', 'NK2', and 'NK3'. The table also has several columns that hold flag values. The flag values are always '0' or '1'. For simplicity, let's call these columns 'test1', 'test2', and 'test3'. So, the table looks like:

CREATE TABLE table1
(
NK1 varchar(255)
,NK2 varchar(255)
,NK3 varchar(255)
,test1 int
,test2 int
,test3 int
)
;

INSERT INTO table1 (NK1, NK2, NK3, test1, test2, test3)
VALUES
('a', 'x', '10', 1, 1, 0)
,('a', 'x', '20', 1, 0, 1)
,('b', 'x', '10', 0, 0, 1)
;

I want to re-arrange the table into the following:

CREATE table table1_rearranged
(
NK1 varchar(255)
,NK2 varchar(255)
,NK3 varchar(255)
,test varchar(255)
,flag int
)
;

where 'test' will hold the names of each of the 'test' columns, and 'flag' will hold the corresponding value from the appropriate 'test' column. So, using the example data above, I want the rearranged table to look like:

INSERT INTO table1_rearranged (NK1, NK2, NK3, test, flag)
VALUES
('a', 'x', '10', 'test1', 1)
,('a', 'x', '10', 'test2', 1)
,('a', 'x', '10', 'test3', 0)
,('a', 'x', '20', 'test1', 1)
,('a', 'x', '20', 'test2', 0)
,('a', 'x', '20', 'test3', 1)
,('b', 'x', '10', 'test1', 0)
,('b', 'x', '10', 'test2', 0)
,('b', 'x', '10', 'test3', 1)

Is there an efficient way to do this (via a SQL query)? I say "efficient" because my actual table1 has lots of 'test'' columns (i.e., the table is wide), and my actual table1 has lots of rows (i.e., the table is deep). (I'm concerned about ease of readability for the query and the execution time.) It was recommended to me to try UNPIVOT, but I am not sure that UNPIVOT is what will achieve this.

Upvotes: 2

Views: 80

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

select t.nk1, t.nk2, t.nk3 ,t.test,t.flag
from table1
unpivot (
  flag
  for test in (test1, test2, test3)
) t;

Upvotes: 1

Serg
Serg

Reputation: 22811

Unpivot will do, alternatively use cross apply 

select NK1, NK2, NK3, test, flag
from table1
cross apply (
    select 'test1' as test, test1 as flag
    union all
    select 'test2' as test, test2 as flag
    union all
    select 'test3' as test, test3 as flag
    ) tv;

Upvotes: 0

Related Questions