May 7, 2016 at 3:53 am
Hi Everybody,
Hi would like to retrieve the information from the below 2 queries in 1 query:
Select Product, Metric, MetricValue
FROM TBL_metrics
WHERE Metric = 'Torque'
Select Product, Metric, MetricValue
FROM TBL_metrics
WHERE Metric = 'BHP'
Ideally I would then be left with results presented in 3 columns like this:
Product / Torque / BHP /
Thanks!!
May 7, 2016 at 6:50 am
will each combo of Product/metric have only one metric value?
edit....forgot to ask...how many unique metrics?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 7, 2016 at 2:35 pm
SELECT * FROM TBL_metrics T
PIVOT (MAX(MetricValue) FOR Metric IN ([Torque], [BHP]) ) P
May 10, 2016 at 6:57 am
Or, if you don't like the PIVOT clause, you could do something like this:
SELECT
Product,
Max(CASE Metric WHEN 'BHP' THEN MetricValue ELSE Null END) AS BHP,
Max(CASE Metric WHEN 'Torque' THEN MetricValue ELSE Null END) AS Torque,
FROM
TBL_metrics
GROUP BY
Product
This works because aggregate functions exclude null values. The case statements null out values you aren't interested in, leaving only the value you are interested in to be returned by the Max function.
May 10, 2016 at 7:50 am
This is how I would do it, but I may not have understood your data fully.
SELECT Torque.Product, Torque.MetricValue [Torque], BHP.MetricValue [BHP]
FROM TBL_metrics Torque
JOIN TBL_metrics BHP ON Torque.Product = BHP.Product
WHERE Torque.Metric = 'Torque'
AND BHP.Metric = 'BHP'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply