Posted by Hugo Kornelis on 01/14/06 00:40

On 12 Jan 2006 23:05:32 -0800, vedavyas.rao@gmail.com wrote:

>I have SQL records as follows
>
>
>
>REGION PRODUCT Amount Week
>IMDS A 100 Week1
>IMDS A 250 Week2
>IMDS A 150 Week3
>
>IMDS B 525 Week1
>IMDS B 400 Week2
>
>IMDS C 500 Week2
>IMDS C 40 Week3
>
>IHYD A 50 Week1
>IHYD A 52 Week2
>IHYD A 100 Week3
>IHYD D 20 Week2
>
>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?

Hi vedavyas.rao,

Actually, doing it client side is much preferred. Presentation should be
handled in the presentation tier, not in the data tier.

However, if you must do it server side, try something like the query
below:

SELECT Region, Product,
SUM(CASE WHEN Week = 'Week1' THEN Amount ELSE 0 END) AS Week1,
SUM(CASE WHEN Week = 'Week2' THEN Amount ELSE 0 END) AS Week2,
SUM(CASE WHEN Week = 'Week3' THEN Amount ELSE 0 END) AS Week3
FROM YourTable
GROUP BY Region, Product

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[Back to original message]