Columns into rows in sql

Question

i would like to ask for help please in sql, I have a table in this form

ID    | Indicator1 | Indicator2 | Indicator3 | Indicator4
1     | 1          | 0          | 0          | 0
2     | 0          | 1          | 1          | 0
3     | 1          | 1          | 0          | 0
4     | 0          | 0          | 0          | 0

And I would like to make it look like this

ID    | Indicators
1     | Indicator1
2     | Indicator2
2     | Indicator3
3     | Indicator1
3     | Indicator2
4     | NULL

Any suggestions please ? Thank you

What DB are you using? It makes a difference on pivot questions. Technically you are asking for an 'unpivot' here. — Twelfth, Mar 28 '18 at 18:07
Should point out that pivot/unpivot is the number one sql question asked on here and there is a silly number of previous questions that will answer this for you. — Twelfth, Mar 28 '18 at 18:09
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — divibisan, Mar 28 '18 at 18:25

score 0 · Answer 1 · answered Mar 28 '18 at 18:31

0

Use UNPIVOT.

SELECT p.Id,
   p.Indicators,
   p.IndicatorsValue 
FROM TableName
UNPIVOT
(
    IndicatorsValue FOR Indicators IN (Indicator1,Indicator2,Indicator3,Indicator4) 
)AS p

answered Mar 28 '18 at 18:31

mvisser

652
5
11

score 0 · Answer 2 · answered Mar 28 '18 at 18:36

0

select   b.* from #temp a left join (
SELECT p.Id,
   p.Indicators
FROM #temp
UNPIVOT
(
    IndicatorsValue FOR Indicators IN (Indicator1,Indicator2,Indicator3,Indicator4) 
)AS p
where p.IndicatorsValue <>0)b on a.id=b.id

answered Mar 28 '18 at 18:36

Daniel Marcus

2,686
1
7
13

replace #temp with your table name – Daniel Marcus Mar 28 '18 at 18:36

score 0 · Answer 3 · answered Mar 29 '18 at 12:35

0

Use UNPIVOT and add filter to remove unnecessary columns:

SELECT  DISTINCT ID, Indicators
FROM #Indicator
UNPIVOT (IndicatorsValue
FOR Indicators IN (Indicator1,Indicator2,Indicator3,Indicator4)) AS up
WHERE up.IndicatorsValue <> 0

answered Mar 29 '18 at 12:35

Aura

1,283
2
16
30

Columns into rows in sql

3 Answers3