none
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


    • Modifié pyram07 lundi 16 avril 2012 03:54
    lundi 16 avril 2012 03:53

Réponses

  • 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

    • Proposé comme réponse Eshani Rao lundi 16 avril 2012 04:57
    • Marqué comme réponse pyram07 lundi 16 avril 2012 13:25
    lundi 16 avril 2012 04:51
  • 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

    • Marqué comme réponse pyram07 lundi 16 avril 2012 21:45
    lundi 16 avril 2012 21:28
    Modérateur

Toutes les réponses

  • 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

    • Proposé comme réponse Eshani Rao lundi 16 avril 2012 04:57
    • Marqué comme réponse pyram07 lundi 16 avril 2012 13:25
    lundi 16 avril 2012 04:51
  • 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

    lundi 16 avril 2012 13:25
  • 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


    • Modifié pyram07 lundi 16 avril 2012 21:09
    lundi 16 avril 2012 15:44
  • anyone please?

    pyram

    lundi 16 avril 2012 17:43
  • 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?


    • Modifié pyram07 lundi 16 avril 2012 21:10
    lundi 16 avril 2012 21:08
  • 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

    • Marqué comme réponse pyram07 lundi 16 avril 2012 21:45
    lundi 16 avril 2012 21:28
    Modérateur
  • 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

    lundi 16 avril 2012 21:45