stored Procedure question

[Islamegy]

In y C# project i'm trying microsoft solution to rotate rows as colums
http://support.microsoft.com/kb/175574/EN-US

SELECT YEAR,
Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
Q.YEAR),0),
Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
Q.YEAR),0),
Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
Q.YEAR),0),
Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
Q.YEAR),0)
FROM QTRSALES Q
GROUP BY YEAR

I can build the Subqueries dynamiclly from code by pass the (Column Alias &
Quarter value) from
pre-query :
SELECT Q_NAME FROM INDECATORS;
and use foreach loop on retrieved rows and replace Q1& Q2...

I want to convert this to one stored procedure so i can pass a YEAR to get
AMOUNT value, is this possible and How??
help plz

 

[Guest]

Something like?

CREATE PROCEDURE GetMeYearInformation
(
@Year int
)
AS

SELECT YEAR,
Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
Q.YEAR),0),
Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
Q.YEAR),0),
Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
Q.YEAR),0),
Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
Q.YEAR),0)
FROM QTRSALES Q
WHERE Year = @Year
GROUP BY YEAR


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Jarmo Muukka]

Hi,

About your SQL statement, I think it could be optimised a bit. Subqueries
are not needed. Here is the procedure and select statement as I would write
it.

create procedure GetSales
@Year int /* if null, gets all years there are */
as
select Year,
Quarter1 = IsNull( Sum( case Quarter when 1 then Amount else
null end ), 0 ),
Quarter2 = IsNull( Sum( case Quarter when 2 then Amount else
null end ), 0 ),
Quarter3 = IsNull( Sum( case Quarter when 3 then Amount else
null end ), 0 ),
Quarter4 = IsNull( Sum( case Quarter when 4 then Amount else
null end ), 0 )
from QtrSales
where Year = @Year or @Year is null
group by Year

JMu