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).