SQL Server Performance

query for create colums from existing data

Discussion in 'T-SQL Performance Tuning for Developers' started by ashoka, Apr 25, 2007.

  1. ashoka New Member

    have a output like

    packageid month no_of_customers
    p1 Jan 25
    p2 Apr 18
    p3 Jan 76
    p1 Mar 20
    p2 May 10

    query for this is like

    select packageid,account.month,count(*) customers
    from packagedetail,account,customer
    where packagedetail.CUSTOMERID = customer.CUSTOMERID
    and customer.ACCOUNTID = account.ACCOUNTID
    group by packageid,account.month;

    now i wann convert in to this format

    packageid Jan apr Mar May
    p1 25 0 20 0
    p2 0 18 0 10
    p3 76 0 0 0

    Please help me for this...what will b the query for the same
  2. FrankKalis Moderator

    Look up cross tab reports in BOL or search this site. This is a FAQ.

    Frank Kalis
    Microsoft SQL Server MVP
  3. Adriaan New Member

    This has been discussed many times ... do a search for cross-tab query.<br /><br />To search this site, note that the built-in Search function of the forum can sometimes give you timeouts. In that case, use this Google search page:<br /<a target="_blank" href=http://www.google.com/advanced_search?q=+site<img src='/community/emoticons/emotion-7.gif' alt=':s' />ql-server-performance.com&hl=en&lr=&as_qdr=all>http://www.google.com/advanced_search?q=+site<img src='/community/emoticons/emotion-7.gif' alt=':s' />ql-server-performance.com&hl=en&lr=&as_qdr=all</a>
  4. ashoka New Member

    actually all the crosstab queries which are mentioned is specially for 1 result means we need to write it for each report....i wanted it as dynamic....

    means that can be run by all the reports.....

    like all the distinct values of tht "month" field will come as columns n other changes also....
  5. FrankKalis Moderator

    There are also way to create dynamic crosstabs. Itzik Ben-Gan has written some great article about that topic for the SQL Server magazine.

    Frank Kalis
    Microsoft SQL Server MVP

Share This Page