den
den

Reputation: 11

SQLite: How to display only column names which have null fields

SQLite:

How to display only column names for null fields in SQLite?

             TABLE_DEMO

ID Column A Column B Column C
1 Drinks Null Null
2 Null Food Travel
3 Null Null Fish

Required Output


ID Null Column
1 Column B
1 Column C
2 Column A
3 Column A
3 Column B

Tried using Case but it was outputting in a single line as comma separated values. Need the column names to be to be in separate rows.


ID Null Column
1 Column B, Column C
2 Column A
3 Column A, Column B

Upvotes: -1

Views: 79

Answers (1)

forpas
forpas

Reputation: 164234

Use UNION ALL:

SELECT ID, 'Column A' AS `Null Column` FROM tablename WHERE `Column A` IS NULL
UNION ALL  
SELECT ID, 'Column B' FROM tablename WHERE `Column B` IS NULL
UNION ALL  
SELECT ID, 'Column C' FROM tablename WHERE `Column C` IS NULL
ORDER BY ID, `Null Column`;

See the demo.

Upvotes: 2

Related Questions