|
|
Posted by Erland Sommarskog on 01/14/06 00:38
(vedavyas.rao@gmail.com) writes:
> REGION PRODUCT Amount Week
> IMDS A 100 Week1
> IMDS A 250 Week2
> IMDS A 150 Week3
>....
> I want to display these items in ASP as
>
>
> REGION PRODUCT Week1 Week2 Week3
>
> IMDS A 100 250 150
> IMDS B 525 400 0
> IMDS C 0 500 40
> IHYD A 50 52 100
> IHYD D 0 20 0
>
>
> Is there any "SQL query" which helps me to bring the above output
> without using any front end calculation?
If your week colunms are fixed, then it is relatively simple:
SELECT region, product,
week1 = SUM(CASE Week WHEN 'Week1' THEN Amount ELSE 0 END),
week2 = SUM(CASE Week WHEN 'Week2' THEN Amount ELSE 0 END),
week3 = SUM(CASE Week WHEN 'Week3' THEN Amount ELSE 0 END),
FROM tbl
GROUP BY region, product
If you can have any number of weeks, you would have to build dynamic
SQL that generated something like the above.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|