Only the last row.

Question

Hi, using sql server 2005.

I have these tables:

CREATE TABLE [dbo].[Proyectos2](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Proyecto] [varchar](255) NULL
)
CREATE TABLE [dbo].[Clientes2](
	
	
	[Vendedora] [varchar](255) NULL,
	[Proyecto] [varchar](255) NULL
	
)


insert into proyectos2
values
(
'Proyecto1'
)
insert into proyectos2
values
(
'Proyecto2'
)
insert into clientes2
values
(
'Jessica','Proyecto1'
)
insert into clientes2
values
(
'Jessica','Proyecto1'
)
insert into clientes2
values
(
'Mariel','Proyecto2'
)

Then, this query:

Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT * FROM CTE UNION ALL ' +
              'SELECT ''Porcentaje'', ' + REPLACE(@Names, '],[', '] / Total, [') + ' / Total, ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

If you execute this code, this is displayed:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------2.000000000000-----0.000000000000-------2
Mariel--------0.000000000000-----1.000000000000-------1
Grand Total---2.000000000000-----1.000000000000-------3
Percentage----0.666666666666-----0.333333333333-------100

I would like something like this:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------------2-------------------0--------------2
Mariel--------------0-------------------1--------------1
Grand Total---------2-------------------1--------------3
Percentage----------0.666666666666------0.333333333333---100

You know, only the percentage row with the decimals.
Is there a way to do this?

thanks

---

pyram

Answer 1

Try this :

Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);
Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';
Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';
SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT Vendedora,cast(floor(Proyecto1) as varchar) Proyecto1,cast(floor(Proyecto2) as varchar)Proyecto2,Total FROM CTE UNION ALL ' +
              'SELECT ''Porcentaje'',cast( ' + REPLACE(@Names, '],[', '] / Total as varchar) Proyecto1, cast([') + ' / Total as varchar), ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';
print @strSQL;
Execute (@strSQL);
GO

Answer 2

Try this :

Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);
Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';
Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';
SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT Vendedora,cast(floor(Proyecto1) as varchar) Proyecto1,cast(floor(Proyecto2) as varchar)Proyecto2,Total FROM CTE UNION ALL ' +
              'SELECT ''Porcentaje'',cast( ' + REPLACE(@Names, '],[', '] / Total as varchar) Proyecto1, cast([') + ' / Total as varchar), ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';
print @strSQL;
Execute (@strSQL);
GO

thanks!

---

pyram

Answer 3

Im trying to get the percentage row with only two numbers after the decimal. Like this:

Percentage----------0.66------0.33---100

Im trying this in this part of the code:

'SELECT ''Porcentaje'',    
   cast(      round(             ' + REPLACE(@Names, '],[', '] / Total as varchar       ,2)      as decimal(10,2)                    ) 
Proyecto
, 
cast([') + ' / Total as varchar)
				,
			 ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

But this gives me: Incorrect syntax near ','

Also im trying to get the percentage for every row:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------------2-------------------0--------------0.66
Mariel--------------0-------------------1--------------0.33
Grand Total---------2-------------------1--------------3

Percentage----------0.66------0.33------------------100

Im trying this:

Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total)       /100          as Total          ';

It says:
Divide by zero error encountered.

What can i do here?

---

pyram

Answer 4

anyone please?

---

pyram

Answer 5

Hi,

i have managed to fix the last row:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------------2-------------------0--------------2

Mariel--------------0-------------------1--------------1

Grand Total---------2-------------------1--------------3

Percentage----------0.66-----------0.33------------------100

Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);
Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';
Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', 
(' + REPLACE(@Names, '],[', '] + [') + ') as Total'
+ 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';
SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT Vendedora,cast(floor(Proyecto1) as varchar) Proyecto1,cast(floor(Proyecto2) as varchar)Proyecto2,Total FROM CTE UNION ALL ' +
              'SELECT ''Porcentaje'',
 str(      cast( ' + REPLACE(@Names, '],[', '] / Total as varchar)       ,10,2) Proyecto1, 
 str(             cast([') + ' / Total as varchar)       ,10,2),
 ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';
print @strSQL;
Execute (@strSQL);
GO
 

But i cant get the code for the percentage for every row in the total column.

Can someone give me a hand in this part?

---

Answer 6

Is it what you want?

Declare @Names As Varchar(Max), @strSQL Varchar(Max), @PercentNames varchar(max) ;
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @PercentNames = Stuff((Select ',' + 'CAST(' + QuoteName(Proyecto)  + '* 100.0/Total AS DECIMAL(10,2))'
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) 
              SELECT *, CAST(Total * 100.0 / (select Total from cte where Vendedora = ''Grand Total'') as Decimal(10,2)) as [Percent]
              
              FROM CTE UNION ALL 
              SELECT ''Porcentaje'', ' + @PercentNames + ', ''100'', 100' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Is it what you want?

Declare @Names As Varchar(Max), @strSQL Varchar(Max), @PercentNames varchar(max) ;
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @PercentNames = Stuff((Select ',' + 'CAST(' + QuoteName(Proyecto)  + '* 100.0/Total AS DECIMAL(10,2))'
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
	Vendedora
	with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) 
              SELECT *, CAST(Total * 100.0 / (select Total from cte where Vendedora = ''Grand Total'') as Decimal(10,2)) as [Percent]
              
              FROM CTE UNION ALL 
              SELECT ''Porcentaje'', ' + @PercentNames + ', ''100'', 100' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Yes. Thanks, you're a saviour.

---

pyram