March 25, 2011 at 1:56 pm
Greetings All,
I have built a select statement from 2 different data models. I am using subqueries to return data from the second model.
It works but is there a better way to approach this particularly from a performance perspective? The number of subqueries may get quite lengthy.
Thanks.
CREATE TABLE #DataModel1
(
ID int,
Analytic1 decimal(3,2),
Analytic2 decimal(3,2),
Analytic3 decimal(3,2),
)
INSERT INTO #DataModel1
SELECT 1, 2.50,9.98,8.35 UNION
SELECT 2, 8.20,9.91,1.25 UNION
SELECT 3, 2.80,9.38,0.25
CREATE TABLE #DataModel2
(
ID int,
AnalyticType varchar(10),
AnalyticValue decimal(3,2),
)
INSERT INTO #DataModel2
SELECT 1,'Analytic4', 1.25 UNION
SELECT 1,'Analytic5', 1.0 UNION
SELECT 1,'Analytic6', 9.0 UNION
SELECT 2,'Analytic4', 1.75 UNION
SELECT 2,'Analytic5', 7.0 UNION
SELECT 2,'Analytic6', 0.0 UNION
SELECT 3,'Analytic4', 1.21 UNION
SELECT 3,'Analytic5', 1.8 UNION
SELECT 3,'Analytic6', 9.2
SELECT
ID,
Analytic1,
Analytic2,
Analytic3,
(SELECT AnalyticValue FROM #DataModel2 WHERE #DataModel1.ID = #DataModel2.ID AND AnalyticType = 'Analytic4') AS Analytic4,
(SELECT AnalyticValue FROM #DataModel2 WHERE #DataModel1.ID = #DataModel2.ID AND AnalyticType = 'Analytic5') AS Analytic5,
(SELECT AnalyticValue FROM #DataModel2 WHERE #DataModel1.ID = #DataModel2.ID AND AnalyticType = 'Analytic6') AS Analytic6
FROM #DataModel1
DROP TABLE #DataModel1
DROP TABLE #DataModel2
March 25, 2011 at 2:17 pm
Try this:
;with cte as
(
select *
from #DataModel2 pivot_Table
pivot
( max(analyticvalue) for AnalyticType in ([Analytic4],[Analytic5],[Analytic6])) pivot_handle
)
SELECT dm1.id, [Analytic1],[Analytic2],[Analytic3] ,[Analytic4],[Analytic5],[Analytic6]
from #DataModel1 DM1
inner join cte cte
on cte.id = dm1.ID
March 28, 2011 at 11:52 am
Thanks, that looks good. I had never used PIVOT before.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply