1

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.

1
  • 1
    You will need to look at the PIVOT syntax, or else create a CASE statement. Oct 12, 2016 at 3:41

1 Answer 1

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

https://technet.microsoft.com/en-us/library/ms177410 for the full pivot reference

3
  • 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, 2016 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, 2016 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, 2016 at 10:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.