SQL Server Performance

Raw Data As Column name

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by juw, Nov 16, 2006.

  1. juw New Member


    I have a table which have Column name "country" having 100 distinct countries
    My requirement is i want to use these countries as a column name

    Like

    Country1_Calls Country2_Calls ----------------------Country100_Calls
    city1
    city2
    city3
    city4


    SELECT city1,
    COUNT(CASE country WHEN 'USA' THEN DURATION ELSE null END) AS Country1_Calls ,
    COUNT(CASE country WHEN 'Canada' THEN DURATION ELSE null END) AS Country2_Calls ,
    From Table
    Group by city1

    For this i have to define 100 times this
    Count COUNT(CASE country WHEN 'USA' THEN DURATION ELSE null END) AS Country1_Calls
    Statement
    Is there Ne other alternative way?
    Plz Help

    juw, Nov 16, 2006
    #2

  2. FrankKalis Moderator

    Since you are on SQL Server 2005, you know the new PIVOT() function?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Nov 16, 2006
    #2

  3. juw New Member

    Plz tell me in detail i have sql server 2000
    juw, Nov 16, 2006
    #2

  4. FrankKalis Moderator

    So, then why do you post in a SQL Server 2005 forum category? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />In SQL Server 2000 there is no PIVOT() function. You either have to do it the way you're doing it now, or, even better, use a client for the crosstab.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Nov 16, 2006
    #2

  5. juw New Member

    Plz Help me in this Crosstab Report which Client i can use for this
    juw, Nov 17, 2006
    #2

  6. FrankKalis Moderator

    Excel, Access. ADO in general...

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Nov 17, 2006
    #2

  7. Madhivanan Moderator

    quote:Originally posted by juw

    Plz Help me in this Crosstab Report which Client i can use for this

    Where do you want to show data?
    If you dont show in front end application, do google search on Dynamic Cross tab + SQL Server

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Nov 17, 2006
    #2

Share This Page