Read data from row and print its corresponding column name-CodePudding

I have the below table created in SQL server:

snapshot_year   snapshot_month  date    customer        active  inactive    old new low high    hard    soft    neutral
2020    2020-12 2020-12 1000216     1   null    null    null    null    null    null    null    null
2021    2021-12 2021-12 1000216     1   null    null    null    null    null    null    null    null
2020    2020-05 2020-05 1000216     null    null    null    null    null    null    1   null    null
2022    2022-01 2022-01 1000216     1   null    null    null    null    null    null    null    null
2020    2020-06 2020-06 1000216     null    null    null    null    null    null    1   null    null
2020    2020-10 2020-10 1000216     null    1   null    null    null    null    null    null    null
2020    2020-09 2020-09 1000216     null    null    1   null    null    null    null    null    null
2021    2021-05 2021-05 1000216     1   null    null    null    null    null    null    null    null
2021    2021-04 2021-04 1000216     1   null    null    null    null    null    null    null    null
2021    2021-08 2021-08 1000216     null    null    null    1   null    null    null    null    null

My requirement is that in each row, it has to read the columns which contain 1, and add a new column called 'segment' with that specific column name as below:

snapshot_year   snapshot_month  date    customer    Segment active  inactive    old new low high    hard    soft    neutral
2020    2020-12 2020-12 1000216 active  1   null    null    null    null    null    null    null    null
2021    2021-12 2021-12 1000216 active  1   null    null    null    null    null    null    null    null
2020    2020-05 2020-05 1000216 hard    null    null    null    null    null    null    1   null    null
2022    2022-01 2022-01 1000216 active  1   null    null    null    null    null    null    null    null
2020    2020-06 2020-06 1000216 hard    null    null    null    null    null    null    1   null    null
2020    2020-10 2020-10 1000216 inactive    null    1   null    null    null    null    null    null    null
2020    2020-09 2020-09 1000216 old null    null    1   null    null    null    null    null    null
2021    2021-05 2021-05 1000216 active  1   null    null    null    null    null    null    null    null
2021    2021-04 2021-04 1000216 active  1   null    null    null    null    null    null    null    null
2021    2021-08 2021-08 1000216 new null    null    null    1   null    null    null    null    null

I'm totally lost on how to get this done, help would be great

CodePudding user response：

If I understand correctly, you can try to use CASE WHEN expression.

Making condition judgment columns if the value was your expectation to display

SELECT *,
       CASE WHEN active = 1 THEN 'active' 
            WHEN inactive = 1 THEN 'inactive'  
            WHEN old = 1 THEN 'old' 
            WHEN new = 1 THEN 'new' 
            WHEN low = 1 THEN 'low'
            WHEN high = 1 THEN 'high'
            WHEN hard = 1 THEN 'hard'
            WHEN soft = 1 THEN 'soft'
            WHEN neutral = 1 THEN 'neutral' END Segment
FROM T