July 20, 2010 at 4:05 am
Hi all..
I want to compare the values of 10 fields in a dataset and display only the major 5 FIELDS (Not top 5 in a column) in a pie chart.
In chart options i tries the single slice/collected pie option. But dont want to group based on a treshold value. Just want to sort the fields based on value and display the top N slices.
Any suggestions???
July 20, 2010 at 12:19 pm
It doesn't really sound like a normalized table design...
Therefore, you might need to use some T-SQL code to UNPIVOT the data, sort it and PIVOT or CrossTab it back in the order you need.
If you need some help how o do it, please post table DDL, sample data and expected result set as described in the first link referenced in my signature.
July 22, 2010 at 2:30 am
Hi Lutz..
Thanks for the response..
July 22, 2010 at 2:50 am
Hi LutZ
Thanks for the response.
I had created the table manually in access. I have jus ported the replica of the access table in SQL server.
The table looks something like this.
Area Date Plant Loss1 Loss2 Loss3 Loss4 Loss5 ....
--------------------------------------------------------------
Area1 2/7/2010 ARPL1 20 10 15 8 5
Area2 3/7/2010 ARPL2 30 0 20 0 0
Area3 4/7/2010 ARPL3 0 0 10 0 0
-------------------------------------------------------------
50 10 45 8 5
I have thousands of records like this.
I need to sumup the losses say TLoss1....TLoss5 and then sort them in descending order and display the major 3 or 4 (in this case) in a Pie chart. (just the major 4 losses). (see in attachment)
The date range and the required area & plant are ontained from the user as input to the Pie chart.
July 22, 2010 at 12:19 pm
Please re-read my previous post and provide data (including expected result) in a ready to use format.
July 23, 2010 at 8:18 am
I have a table somewhat like the one you're describing. I haven't ever done what you were looking for but I guess this is how I would do it with my table. Mabye you can take the below as a template and use your table info. Basically the query uses 3 temp tables along whe way to normalize your data, rank it and then de-normalize back the form your needing. Then sum the results and order it. Hope this helps.
WITH NORMAL AS
(
SELECT CASE_NO,
ID ,
EFF_DT,
FD_1_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_2_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_3_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_4_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_5_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_6_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_7_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_8_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_9_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
UNION ALL
SELECT CASE_NO,
ID ,
EFF_DT,
FD_10_ELEC_PCT AS ELEC
FROM ELECTIONS
WHERE CASE_NO = 'JK62202 00001'
AND ID <= '333333333'
),
RANKED_ORDER AS
(
SELECT CASE_NO, ID, EFF_DT, ELEC, ROW_NUMBER() OVER(PARTITION BY CASE_NO, ID, EFF_DT ORDER BY ELEC DESC) AS RANKING
FROM NORMAL
),
UNORMAL AS
(
SELECT DISTINCT CASE_NO, ID, EFF_DT,
(SELECT ELEC FROM RANKED_ORDER B WHERE A.CASE_NO = B.CASE_NO AND A.ID = B.ID AND A.EFF_DT = B.EFF_DT AND RANKING = 1) AS ELEC_1,
(SELECT ELEC FROM RANKED_ORDER B WHERE A.CASE_NO = B.CASE_NO AND A.ID = B.ID AND A.EFF_DT = B.EFF_DT AND RANKING = 2) AS ELEC_2,
(SELECT ELEC FROM RANKED_ORDER B WHERE A.CASE_NO = B.CASE_NO AND A.ID = B.ID AND A.EFF_DT = B.EFF_DT AND RANKING = 3) AS ELEC_3,
(SELECT ELEC FROM RANKED_ORDER B WHERE A.CASE_NO = B.CASE_NO AND A.ID = B.ID AND A.EFF_DT = B.EFF_DT AND RANKING = 4) AS ELEC_4,
(SELECT ELEC FROM RANKED_ORDER B WHERE A.CASE_NO = B.CASE_NO AND A.ID = B.ID AND A.EFF_DT = B.EFF_DT AND RANKING = 5) AS ELEC_5
FROM RANKED_ORDER A
)
SELECT CASE_NO, ID, EFF_DT, ELEC_1, ELEC_2, ELEC_3, ELEC_4, ELEC_5, (ELEC_1 + ELEC_2 + ELEC_3 + ELEC_4 + ELEC_5) AS TOTAL
FROM UNORMAL
ORDER BY TOTAL DESC
July 24, 2010 at 1:40 am
Thanks for the efforts..
I have fixed it up by using unpivot.. Thanks lutz for the spark.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply