Transpose table and dump it by columns

Question

I have a following table:

id code bool1 bool2 bool3 bool4
1  A    true  true  false true
2  B    false true  true  true

Is it possible to get something like this?

id code bool
1  A    name_for_bool1  // name of column bool1 e.g. 'worker'
1  A    name_for_bool2
1  A    name_for_bool4
2  A    name_for_bool2
2  A    name_for_bool3
2  A    name_for_bool4

I would like to tell you what I've tried, but it has been completelly wrong - I have no idea how to do it..

select id, code, 'worker' from table where bool1 union all etc — jarlh, Feb 27 '15 at 10:07
If you are using SQL Server 2008 or later you can use [`UNPIVOT`](https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx) — GarethD, Feb 27 '15 at 10:11

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

Check this ... Unpivot

declare @t table(id int, code varchar(50), bool1 varchar(50),bool2 varchar(50),bool3 varchar(50),bool4 varchar(50))
insert into @t values (1,'A','true','true','false','true')
,(2,'B','false','true','true','true')

select * from @t

select id, code , boolvalue
from 
@t
unpivot
(
  boolvalue
  for boolvalue1 in (bool1, bool2,bool3,bool4)

) u;

edited May 23 '17 at 11:56

Community

1
1

answered Feb 27 '15 at 10:16

Ajay2707

5,690
6
40
58

score 1 · Answer 2 · answered Feb 27 '15 at 10:54

How about this:

SELECT id, code, 'name_for_bool1' bool FROM src WHERE bool1
UNION ALL
SELECT id, code, 'name_for_bool2' bool FROM src WHERE bool2
UNION ALL
SELECT id, code, 'name_for_bool3' bool FROM src WHERE bool3
UNION ALL
SELECT id, code, 'name_for_bool4' bool FROM src WHERE bool4

Transpose table and dump it by columns

2 Answers2