[Resolved] Get total from rows, dynamic query

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 2005 Forums

Transact-SQL (2005)

[Resolved] Get total from rows, dynamic query

Author

Topic

pyram
Starting Member

12 Posts

Posted - 2012-04-12 : 09:26:53

Hi,
I have my table:


Create Table SalesmanProjects 
(
	Salesman Varchar(10) 
	,Projects Varchar(20)
)

Inserting data:


Insert Into SalesmanProjects
Select 'John', 'ProjectOne' Union All 
Select 'Mark', 'ProjectTwo' Union all 
Select 'John', 'ProjectOne'

a select with pivot on the table:


Declare @Names As Varchar(Max), @strSQL Varchar(Max)
Select @Names = Stuff((Select ',' + QuoteName(Projects) From SalesmanProjects Group By Projects Order by Projects For XML Path('')), 1, 1, '')
Set @strSQL = 'Select Salesman, ' + @Names + ' From (Select Salesman, Projects From SalesmanProjects) P Pivot (Count(Projects) For Projects in (' + @Names + ')) As Pvt'
print @strSQL
Execute (@strSQL)

The query is about how many times a salesman is assigned to a project.

The query outputs:
Salesman---ProjectOne---ProjectTwo
John------------2------------0
Mark------------0------------1

What i would like is an output like this:

John----2----0---2
Mark----0----1---1

So it will add the first two numbers and it will display the total at the end.

How can i do this?

thanks...

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-04-13 : 07:13:03

Use this:


Set @strSQL = 'Select Salesman, ' + @Names + ', ' + REPLACE(@Names, ',', '+') + ' AS Total' +
              ' From (Select Salesman, Projects From SalesmanProjects) P Pivot (Count(Projects) For Projects in (' + @Names + ')) As Pvt'

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:12:07

if this query is for reporting tool, you can very easily generate the total rows inline in reporting tool itself.

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

pyram
Starting Member

12 Posts

Posted - 2012-04-13 : 16:29:35

quote:
Originally posted by malpashaa

Use this:

Set @strSQL = 'Select Salesman, ' + @Names + ', ' + REPLACE(@Names, ',', '+') + ' AS Total' +
' From (Select Salesman, Projects From SalesmanProjects) P Pivot (Count(Projects) For Projects in (' + @Names + ')) As Pvt'

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

thanks

pyram
Starting Member

12 Posts

Posted - 2012-04-13 : 16:31:09

quote:
Originally posted by visakh16

if this query is for reporting tool, you can very easily generate the total rows inline in reporting tool itself.

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

no, its for using in a website.
thanks.

pyram
Starting Member

12 Posts

Posted - 2012-04-14 : 12:16:28

quote:
Originally posted by visakh16

if this query is for reporting tool, you can very easily generate the total rows inline in reporting tool itself.

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

I have zero experience with reporting tools.
You are saying that one can generate the totals with the reporting tool.

Does this means that i can insert the query in the reporting tool and the tool will also generate the sql code for the total?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-15 : 13:11:28

quote:
Originally posted by pyram

quote:
Originally posted by visakh16

if this query is for reporting tool, you can very easily generate the total rows inline in reporting tool itself.

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

I have zero experience with reporting tools.
You are saying that one can generate the totals with the reporting tool.

Does this means that i can insert the query in the reporting tool and the tool will also generate the sql code for the total?

yep. you've options to automtically add totals based on individual values in report. the aggregation will be done at report server end and not in sql

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

pyram
Starting Member

12 Posts

Posted - 2012-04-15 : 22:50:26

quote:
Originally posted by visakh16

quote:
Originally posted by pyram

quote:
Originally posted by visakh16

if this query is for reporting tool, you can very easily generate the total rows inline in reporting tool itself.

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

I have zero experience with reporting tools.
You are saying that one can generate the totals with the reporting tool.

Does this means that i can insert the query in the reporting tool and the tool will also generate the sql code for the total?

yep. you've options to automtically add totals based on individual values in report. the aggregation will be done at report server end and not in sql

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

i see, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 11:56:02

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources