How to convert column names to multiple rows?

Question

I've a legacy database containing a table with multiple columns of type boolean. E.g.:

Table_1

id name           has_lights has_engine has_brakes  has_tyres can_move
1  bullock_cart   false      false      false       true      true
2  car            true       true       true        true      true
3  tank           true       true       true        false     true

I'd like to write an SQL query for Table1 to fetch the id and name and the attributes (represented by the name of the column) that are true.

Expected output:

id name        attributes
-- ----        ----------
1 bullock_cart has_tyres
1 bullock_cart can_move
2 car          has_lights
2 car          has_engine
2 car          has_brakes
2 car          has_tyres
2 car          can_move
3 tank         has_lights
3 tank         has_engine
3 tank         has_brakes
3 tank         can_move

I wrote:

SELECT id, name,
CASE
  WHEN has_lights THEN 'has_lights'
  WHEN has_engine THEN 'has_engine'
  WHEN has_brakes THEN 'has_brakes'
  WHEN has_tyres THEN 'has_tyres'
  WHEN can_move THEN 'can_move'
END
FROM TABLE1;

But this gets me only the 1st matching attribute for each row in Table1 (by virtue of CASE-WHEN).

What is the correct way to retrieve the data in the format I want? Any inputs/help will be greatly appreciated?

Notes:

The table structure isn't ideal but this is a legacy system and we cannot modify the schema.
Nested queries are ok as long as they aren't too slow - say for the sample above (I understand the number of matching rows/column factor in the slow-ness).

Gordon Linoff · Accepted Answer · 2016-09-08 12:06:23Z

4

The simplest method is union all:

select id, name, 'has_lights' as attribute from t where has_lights union all
select id, name, 'has_engine' from t where has_engine union all
select id, name, 'has_brakes' from t where has_brakes union all
select id, name, 'has_tyres' from t where has_tyres union all
select id, name, 'can_move' from t where can_move;

If you have a very large table, then a lateral join is probably more efficient:

select t.id, t.name, v.attribute
from t, lateral
     (select attribute
      from (values (has_lights, 'has_lights'),
                   (has_engine, 'has_engine'),
                   (has_brakes, 'has_brakes'),
                   (has_tyres, 'has_tyres'),
                   (can_move, 'can_move')
           ) v(flag, attribute)
      where flag
     ) v;

answered Sep 8, 2016 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sagi · Accepted Answer · 2016-09-08 12:06:58Z

2

You can do it using UNION ALL :

SELECT name,'has_lights' as attributes FROM YourTable where has_lights = 'TRUE'
UNION ALL
SELECT name,'has_engine' as attributes FROM YourTable where has_engine= 'TRUE'
UNION ALL
SELECT name,'has_brakes' as attributes FROM YourTable where has_brakes = 'TRUE'
UNION ALL
SELECT name,'has_tyres' as attributes FROM YourTable where has_tyres = 'TRUE'
UNION ALL
SELECT name,'can_move' as attributes FROM YourTable where can_move = 'TRUE'

answered Sep 8, 2016 at 12:06

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:22:53Z

1

This is much like the brilliant query @Gordon posted:

SELECT t.id, t.name, v.attribute
FROM   table1 t
JOIN   LATERAL (
   VALUES (has_lights, 'has_lights')
        , (has_engine, 'has_engine')
        , (has_brakes, 'has_brakes')
        , (has_tyres , 'has_tyres')
        , (can_move  , 'can_move')
   ) v(flag, attribute) ON v.flag;

Just a bit shorter since the VALUES expression can stand on its own.

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Sep 8, 2016 at 15:22

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

How to convert column names to multiple rows?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related