SQL Server Performance Forum – Threads Archive
query for create colums from existing data
have a output like packageid month no_of_customersp1 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
Look up cross tab reports in BOL or search this site. This is a FAQ. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
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>
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….
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
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
]]>