Reputation: 11
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
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