Dynamic pivot sql to get the total

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

General SQL Server Forums

New to SQL Server Programming

Dynamic pivot sql to get the total

Author

Topic

sindhu sarah
Starting Member

34 Posts

Posted - 2012-04-23 : 04:38:44

Hi,

I have the following query

Select @Cols1 = STUFF(( SELECT DISTINCT '],[' + Attendance
FROM Ezbusdb.Timesheets WHERE ProjectID = '' AND
convert(varchar(8),Dates,112)=convert(varchar(8),@Date,112)
and Entity=@Entity and attendance <> '' ORDER BY '],[' + Attendance
FOR XML PATH('')), 1, 2, '') + ']'

Select @Cols2 = STUFF(( SELECT DISTINCT '],[' + ProjectID
FROM Ezbusdb.Timesheets WHERE ProjectID <> '' AND
convert(varchar(8),Dates,112)=convert(varchar(8),@Date,112)
and Entity=@Entity ORDER BY '],[' + ProjectID
FOR XML PATH('')), 1, 2, '') + ']'

Set @Cols3 = @Cols1+','+@Cols2

Set @Query2 = N'select distinct Dept, CC, EID, EName, '+@Cols3 +' From
(
Select Distinct Dept, CC, EID, EName, HC, ProjId From
(
Select AxEmpl.Dept as Dept, AxCC.CC, AxEmpl.EID, AxEmpl.EName,
Rank() over (partition by Timesheets.EID Order By Timesheets.ProjectId DESC) As ProjRank,
Timesheets.EID as HC,
ProjId = Case(Timesheets.ProjectID) when '''' Then Attendance Else ProjectID End
From Ezbusdb.Timesheets, Ezbusdb.AxEmpl, EzBusDb.AxCC where
Timesheets.EID = AxEmpl.EID and Timesheets.Entity = AxEmpl.Entity and
AxCC.CC = AxEmpl.CC and AxCC.Entity = AxEmpl.Entity and Left(Acct,6) = '''+@act+''' and
Timesheets.Entity ='''+@Entity+''' and
convert(Varchar(8),Timesheets.Dates,112)='''+convert(Varchar(8),@Date,112)+'''
)
as AA where ProjRank = 1
) P
PIVOT (Count([HC]) FOR projid IN ('+ @Cols3 +')) as Pvt;'

The result set will be
Dept CC EID A L LWP SL 100001 100292 100304 100326 100327
120 12001 E000011 0 0 0 0 1 0 0 0 0
120 12003 E003105 0 0 0 0 0 0 0 0 0
120 12003 E003396 0 0 0 0 0 0 0 0 0
120 12004 E003374 0 0 0 0 0 0 0 0 0
120 12004 E003378 0 0 0 0 0 0 0 0 0
120 12004 E003379 0 0 0 0 0 0 0 0 0
120 12004 E003392 0 0 0 0 0 0 0 0 0
120 12004 E003393 0 1 0 0 0 0 0 0 0
120 12005 E004113 0 0 0 0 0 0 0 0 0
120 12005 E004200 0 0 0 0 0 1 0 0 0
120 12005 E004205 0 0 0 0 0 0 0 0 0
120 12005 E004353 0 0 0 0 0 0 0 0 0

But i need the total for each CC like
Dept CC EID A L LWP SL 100001 100292 100304 100326 100327
120 12001 E000011 0 0 0 0 1 0 0 0 0
Total 0 0 0 0 1 0 0 0 0
120 12003 E003105 0 0 0 0 0 0 0 0 0
120 12003 E003396 0 0 0 0 0 0 0 0 0
Total 0 0 0 0 0 0 0 0 0
120 12004 E003374 0 0 0 0 0 0 0 0 0
120 12004 E003378 0 0 0 0 0 0 0 0 0
120 12004 E003379 0 0 0 0 0 0 0 0 0
120 12004 E003392 0 0 0 0 0 0 0 0 0
120 12004 E003393 0 1 0 0 0 0 0 0 0
Total 0 1 0 0 0 0 0 0 0
120 12005 E004113 0 0 0 0 0 0 0 0 0
120 12005 E004200 0 0 0 0 0 1 0 0 0
120 12005 E004205 0 0 0 0 0 0 0 0 0
120 12005 E004353 0 0 0 0 0 0 0 0 0
Total 0 0 0 0 0 1 0 0 0

For exampl 12001 is a cost centre and i need te total in between that record set.
Please let me know how to use total functions with dynamic pivot query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 11:44:56

is this for a reporting requirement? if yes you can very easily get this as reporting end using GROUPS and putting aggregate expressions in grouping footers.

If you want to do this in sql you need to apply group by and use with cube or with rollup

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2012-04-24 : 10:21:34

Thanks for your response.but for pivot group by and rollup is not working with dynamic pivot.Please can you show me some samples?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:27:11

you need to first apply group by roolup/cube and then apply pivot over that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2012-04-25 : 08:19:09

ya that also did not work.
Set @Query2 = N'select distinct Dept, CC, EID, EName, '+@Cols3 +' From
(
Select Distinct Dept, CC, EID, EName, HC, ProjId From
(
Select AxEmpl.Dept as Dept, AxCC.CC, AxEmpl.EID, AxEmpl.EName,
Rank() over (partition by Timesheets.EID Order By Timesheets.ProjectId DESC) As ProjRank,
Timesheets.EID as HC,
ProjId = Case(Timesheets.ProjectID) when '''' Then Attendance Else ProjectID End
From Ezbusdb.Timesheets, Ezbusdb.AxEmpl, EzBusDb.AxCC where
Timesheets.EID = AxEmpl.EID and Timesheets.Entity = AxEmpl.Entity and
AxCC.CC = AxEmpl.CC and AxCC.Entity = AxEmpl.Entity and Left(Acct,6) = '''+@act+''' and
Timesheets.Entity ='''+@Entity+''' and
convert(Varchar(8),Timesheets.Dates,112)='''+convert(Varchar(8),@Date,112)+'''
group by AxEmpl.Dept,AxCC.CC,AxEmpl.EID, AxEmpl.EName, Timesheets.EID ,
Timesheets.ProjectId,Timesheets.EID with rollup
)
as AA where ProjRank = 1
) P
PIVOT (Count([HC]) FOR projid IN ('+ @Cols3 +')) as Pvt;'

please give me any sample query for dynamic sql with group by rollup.

sindhu sarah
Starting Member

34 Posts

Posted - 2012-04-25 : 08:50:01

i used to grouping and my output is near by.but i feel something is missing.i have writend the print statement of dynamic query

select distinct Dept, CC, EID,ccc, EName, [A],[L],[LWP],[SL],[100001],[100292],[100304] From
(
Select Distinct Dept,CASE
WHEN (Grouping(CC)=1) THEN 'Dept Total'
ELSE CC
END AS CC,CASE
WHEN (Grouping(EID)=1) THEN 'CC Total'
ELSE EId
END AS EID, count(CC) as CCC,EName, HC, ProjId From
(
Select AxEmpl.Dept as Dept, AxCC.CC, AxEmpl.EID, AxEmpl.EName,
Rank() over (partition by Timesheets.EID Order By Timesheets.ProjectId DESC) As ProjRank,
Timesheets.EID as HC,
ProjId = Case(Timesheets.ProjectID) when '' Then Attendance Else ProjectID End
From Ezbusdb.Timesheets, Ezbusdb.AxEmpl, EzBusDb.AxCC where
Timesheets.EID = AxEmpl.EID and Timesheets.Entity = AxEmpl.Entity and
AxCC.CC = AxEmpl.CC and AxCC.Entity = AxEmpl.Entity and Left(Acct,6) = '000000' and
Timesheets.Entity ='cae' and
convert(Varchar(8),Timesheets.Dates,112)='20110331'

)
as AA where ProjRank = 1 group by Dept,CC,EID, EName,HC,
ProjId with rollup
) P
PIVOT (Count([HC]) FOR projid IN ([A],[L],[LWP],[SL],[100001],[100292],[100304])) as Pvt;

output:
NULL Dept Total CC Total 818 NULL 0 0 0 0 0 0 0
120 12001 CC Total 1 NULL 0 0 0 0 0 0 0
120 12001 E000011 1 NULL 0 0 0 0 0 0 0
120 12001 E000011 1 abcname 0 0 0 0 1 0 0
120 12003 CC Total 3 NULL 0 0 0 0 0 0 0
120 12003 E003105 2 NULL 0 0 0 0 0 0 0
120 12003 E003105 2 xyzname 0 0 0 0 0 0 0
120 12003 E003396 1 NULL 0 0 0 0 0 0 0
120 12003 E003396 1 qwyname 0 0 0 0 0 0 0
120 12004 CC Total 5 NULL 0 0 0 0 0 0 0

but the eid is repeating .I want the grouping of CC alone.please help me how to eliminate the other fields from group by

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-25 : 14:32:55

first populate a temporary table with results of rollup and then use pivot over that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2012-04-26 : 07:07:04

thanks for ur response.am not able to make a temp table since it is dynamic fields.Kindly advice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 10:51:33

why not create table on the fly inside dynamic sql using select ...into

remember to drop it once you apply pivot

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources