How to create 3 columns from 2 columns with SQL query?

Question

I have the following table, Table1:

Id, Speed, Power
1, 1000, 100
1, 2000, 150
1, 3000, 200
2, 1000, 125
2, 2000, 175
2, 3000, 225

I would like to know SQL syntax to return the following:

Speed, Power600V, Power750V
1000, 100, 125
2000, 150, 175
3000, 200, 225

I tried this:

SELECT Speed, Power as Power600V From Table1 WHERE Id=1
UNION
SELECT Speed, Power as Power750V From Table1 WHERE Id=2

It isn't returning the data the right way though.

You will need to look at the PIVOT syntax, or else create a CASE statement. — Randolph West, Oct 12 '16 at 03:41

score 0 · Answer 1 · answered Oct 12 '16 at 09:25

0

You need to use a pivot table to get colums out of rows for what you're after

select speed, idp.[1] as power600V,idp.[2] as power750V
from tableName PIVOT
(
MAX(power)
FOR id in([1],[2])
) as idp

will get you the results you're after

answered Oct 12 '16 at 09:25

Ste Bov

SELECT [Speed_Rpm], idp.[1] as Power600V, idp.[2] as Power750V FROM [5007_MotorsCurvesData] PIVOT ( MAX ([PeakPower_kW]) FOR [5007_MotorCurvesInfos] in([1],[2]) ) as idp – Dotrick Oct 12 '16 at 13:14
Thank you, I tried this: SELECT [Speed_Rpm], idp.[1] as Power600V, idp.[2] as Power750V FROM [5007_MotorsCurvesData] PIVOT ( MAX ([PeakPower_kW]) FOR [5007_MotorCurvesInfos] in([1],[2]) ) as idp But it doesn't work as i want and I would like to add a WHERE and I can't... Like This: SELECT [Speed_Rpm], idp.[1] as Power600V, idp.[2] as Power750V FROM [5007_MotorsCurvesData] WHERE [5007_MotorCurvesInfos] IN (7,8) PIVOT ( MAX ([PeakPower_kW]) FOR [5007_MotorCurvesInfos] in([1],[2]) ) as idp – Dotrick Oct 12 '16 at 13:19
Am confused the `5007_MotorCurvesInfos` field you're mentioning you've wrote to pivot on (7,8) but where its in (1,2) even if where statements could be used with pivot you'd not get any results from this, if you negate one of the ids you'll lose an entire column of data – Ste Bov Oct 13 '16 at 10:05

1 Answers1