I have a table in SQL that has four columns. Two of these are property and value columns that store performance metrics for one of our servers. We're constantly coming up with new performance metrics and we didn't want to keep redesigning our schema, so thats why we designed the table this way.
Trouble is, when I create a view to look at the table as if its properly normalized, I get a query that just screams "Oh my god this is crap code" since it involves a table joined to itself 12 times. Here is the query I used for the view.
Basically, it feels like I'm doing something really wrong, but I can't figure out a better way to solve the problem.
SELECT
astats.AQTORStatsID,
astats.ServerName,
astats.Remarks,
astats.StatsBeginDateTime,
astats.StatsEndDateTime,
asi1.AQTORStatValue as 'QtCPU_Average',
asi2.AQTORStatValue as 'QtCPU_TopQuintile',
asi3.AQTORStatValue as 'QtCPU_TopOnePercent',
asi4.AQTORStatValue as 'QtCl_Average',
asi5.AQTORStatValue as 'QtCl_TopQuintile',
asi6.AQTORStatValue as 'QtCl_TopOnePercent',
asi7.AQTORStatValue as 'UpdPrcStd_Average',
asi8.AQTORStatValue as 'UpdPrcStd_TopQuintile',
asi9.AQTORStatValue as 'UpdPrcStd_TopOnePercent',
asi10.AQTORStatValue as 'RcRsUPr_Average',
asi11.AQTORStatValue as 'RcRsUPr_TopQuintile',
asi12.AQTORStatValue as 'RcRsUPr_TopOnePercent'
FROM
tb_rAQTORStatsItem asi1
INNER JOIN tb_rAQTORStatsItem asi2 ON asi1.AQTORStatsID = asi2.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi3 ON asi2.AQTORStatsID = asi3.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi4 ON asi3.AQTORStatsID = asi4.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi5 ON asi4.AQTORStatsID = asi5.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi6 ON asi5.AQTORStatsID = asi6.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi7 ON asi6.AQTORStatsID = asi7.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi8 ON asi7.AQTORStatsID = asi8.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi9 ON asi8.AQTORStatsID = asi9.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi10 ON asi9.AQTORStatsID = asi10.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi11 ON asi10.AQTORStatsID = asi11.AQTORStatsID
INNER JOIN tb_rAQTORStatsItem asi12 ON asi11.AQTORStatsID = asi12.AQTORStatsID
INNER JOIN tb_dAQTORStats astats on asi12.AQTORStatsID = astats.AQTORStatsID
WHERE
asi1.AQTORStatName = 'QtCPU_Average'
AND asi2.AQTORStatName = 'QtCPU_TopQuintile'
AND asi3.AQTORStatName = 'QtCPU_TopOnePercent'
AND asi4.AQTORStatName = 'QtCl_Average'
AND asi5.AQTORStatName = 'QtCl_TopQuintile'
AND asi6.AQTORStatName = 'QtCl_TopOnePercent'
AND asi7.AQTORStatName = 'UpdPrcStd_Average'
AND asi8.AQTORStatName = 'UpdPrcStd_TopQuintile'
AND asi9.AQTORStatName = 'UpdPrcStd_TopOnePercent'
AND asi10.AQTORStatName = 'RcRsUPr_Average'
AND asi11.AQTORStatName = 'RcRsUPr_TopQuintile'
AND asi12.AQTORStatName = 'RcRsUPr_TopOnePercent'