SQL QUERY HELP

[vedavyas.rao]

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?

[Erland Sommarskog]

(ve**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 12 Jan 2006 23:05:32 -0800, ve**********@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