Pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pivot table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-24 : 07:50:39

Dejan writes "I read that my question shouldn't be on any forums, but I didn't know about this site untill now. It is posted on one forum only.
Anyway, I went through article "Dynamic Cross-Tabs/Pivot Tables" on your site, but I don't get it. Not enough expirience I guess.
I'm having hard time with transfering one query to SQL
Stored Procedure. In Access 2000 It looks like this:

PARAMETERS [Please Enter State Code] Text ( 255 );
TRANSFORM Count([Month]) AS Expr1
SELECT tGroup.GrpYear, tGroup.StPr,
tRegionCrossRefUS.Region, Count([Month]) AS StateTotal
FROM tGroup INNER JOIN tRegionCrossRefUS ON tGroup.StPr =
tRegionCrossRefUS.StateUSCode
WHERE (((tGroup.GrpYear)>1989) AND ((tGroup.StPr)=[Please
Enter State Code]) AND ((tGroup.GrpStatus)="C" Or
(tGroup.GrpStatus)="I" Or (tGroup.GrpStatus)="X" Or
(tGroup.GrpStatus)="InProg" Or (tGroup.GrpStatus)="Comp")
AND ((tGroup.GrpLanguage)="English" Or
(tGroup.GrpLanguage) Is Null))
GROUP BY tGroup.GrpYear, tGroup.StPr,
tRegionCrossRefUS.Region
PIVOT IIf([GrpDateSchedStart] Is Not Null,Month
([GrpDateSchedStart]),IIf([GrpDateSchedStart] Is Null And
[DateWk1Rpt] Is Not Null,Month([DateWk1Rpt]),IIf(Month
([DateGrpOrder]) Is Not Null,Month([DateGrpOrder]),IIf
((Month([DateCertSent])-3)<1,IIf(Year([DateCertSent])=
[GrpYear],1,10),Month([DateCertSent])-3)))) In
(1,2,3,4,5,6,7,8,9,10,11,12);

Instead of getting different amount for each month, for
specific year, I get the same amount for jan,feb,.... for
each year. My Stored Procedure looks like this:

Alter PROCEDURE [Qr-GroupStartsUSSt]
@Please_Enter_State_Code nvarchar(2)
AS
select distinct g.GrpYear, @Please_Enter_State_Code as
StPr,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') ) StateTotal,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=1) Jan,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=2) Feb,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=3) Mar,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=4) Apr,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=5) May,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=6) Jun,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=7) Jul,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=8) Aug,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=9) Sep,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=10) Oct,
(select COUNT(DateGrpOrder) from dbo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources