How to sort data in a row?

  • 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???

  • 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.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi Lutz..

    Thanks for the response..

  • 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.

  • Please re-read my previous post and provide data (including expected result) in a ready to use format.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_2_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_3_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_4_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_5_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_6_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_7_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_8_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_9_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    UNION ALL

    SELECT CASE_NO,

    ID ,

    EFF_DT,

    FD_10_ELEC_PCT AS ELEC

    FROMELECTIONS

    WHERE CASE_NO = 'JK62202 00001'

    AND ID <= '333333333'

    ),

    RANKED_ORDER AS

    (

    SELECTCASE_NO, ID, EFF_DT, ELEC, ROW_NUMBER() OVER(PARTITION BY CASE_NO, ID, EFF_DT ORDER BY ELEC DESC) AS RANKING

    FROMNORMAL

    ),

    UNORMAL AS

    (

    SELECTDISTINCT 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

    FROMRANKED_ORDER A

    )

    SELECTCASE_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

    FROMUNORMAL

    ORDER BYTOTAL DESC

  • 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