Change data arrangement
-
Hey, i have a table with several columns Q1 to Q9, ID, datetime, ID is the same for a given group of data. Each column Q1 to Q9 can have up to 12(-2 - 9) values in it, and 10 to hundreds of thousands of rows per ID(dont ask it does make sense i just dont feel like explaining it). and i'm trying to make a report in CR 2008 but i cant get it generate graphs of all the occurances of values in all 9 columns w/out 9 graphs. so the only solution i can think of is to change the columns in to rows and make the differnt possible values columns like Question -2s -1s 0s 1s 2s ...9s Q1 3 0 0 2 1 (a count of the number of times each value appears) i'm managed it with this, long but simple, and suprisingly speedy
SELECT 'Q1' AS Question,
COUNT(Q1) - COUNT(NULLIF(Q1,-2)) AS '-2s',
COUNT(Q1) - COUNT(NULLIF(Q1,-1)) AS '-1s',
COUNT(Q1) - COUNT(NULLIF(Q1,0)) AS '0s',
COUNT(Q1) - COUNT(NULLIF(Q1,1)) AS '1s',
COUNT(Q1) - COUNT(NULLIF(Q1,2)) AS '2s',
COUNT(Q1) - COUNT(NULLIF(Q1,3)) AS '3s',
COUNT(Q1) - COUNT(NULLIF(Q1,4)) AS '4s',
COUNT(Q1) - COUNT(NULLIF(Q1,5)) AS '5s',
COUNT(Q1) - COUNT(NULLIF(Q1,6)) AS '6s',
COUNT(Q1) - COUNT(NULLIF(Q1,7)) AS '7s',
COUNT(Q1) - COUNT(NULLIF(Q1,8)) AS '8s',
COUNT(Q1) - COUNT(NULLIF(Q1,9)) AS '9s'
FROM table
UNION
-- repeat query/union for all 9 Q columnspersonally i think this way sucks, and there has to be a better one, but i'm at a loss as to what it is. Any help would be appreciated, oh and because i'll know very little about the data before running the report and it will be accessed from a web app i dont think a temp table would work very well either, and it wouldnt be as simplistic.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.