I am trying to convert 3 columns into 2. Is there a way I can do this with the example below or a different way?
For example.
Year Temp Temp1
2015 5 6
Into:
Year Value
Base 5
2015 6
Asked
Active
Viewed 159 times
4
Taryn
- 234,956
- 54
- 359
- 399
TheProgrammer
- 1,118
- 3
- 17
- 38
-
Which DBMS are you using? E.g., there's a `PIVOT` operator for this, but it exists in MSSQL only. – Smileek May 04 '16 at 13:39
-
SQL Server is the one I am using – TheProgrammer May 04 '16 at 13:40
-
2Why does 6 gets 2015 as year, and 5 gets base? – sagi May 04 '16 at 13:42
-
@sagi Anything for temp1 gets assigned to 2015 and anything for temp gets assigned to baseline – TheProgrammer May 04 '16 at 13:44
-
1@DeVonte This is an unpivot not a pivot. – Taryn May 04 '16 at 13:48
3 Answers
5
This is called unpivot, pivot is the exact opposite(make 2 columns into more) .
You can do this with a simple UNION ALL:
SELECT 'Base',s.temp FROM YourTable s
UNION ALL
SELECT t.year,t.temp1 FROM YourTable t
This relays on what you wrote on the comments, if year is constant , you can replace it with '2015'
sagi
- 38,630
- 5
- 53
- 82
-
-
1@RichBenner Hah didn't notice I didn't actually alias the tables. Thanks. – sagi May 04 '16 at 13:50
-
If `Year` is number you will get conversion error. Second first column is not named – Lukasz Szozda May 04 '16 at 13:54
-
I assumed by his example that he contains the year as a string. I guess it was since he said it worked. @lad2025 – sagi May 04 '16 at 13:55
2
You could use CROSS APPLY and row constructor:
SELECT s.*
FROM t
CROSS APPLY(VALUES('Base', Temp),(CAST(Year AS NVARCHAR(100)), Temp1)
) AS s(year,value);
Lukasz Szozda
- 139,860
- 19
- 198
- 228
2
There is more than one answer to your question. Using UNION ALL seems to be the most simple solution.
I suggest reading this thread Converting Columns into rows with their respective data in sql server, as it provides a lot more details, and you can try and test how different solutions will work for you.
