Pivoter table in SP Sql Server

Question

Pivoter table in SP Sql Server

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (0 votes)

1

I have a case where I have to pivot a temp table that I use in SP, this table must have a format by Number of Month example:

My sp executes the following :, but to achieve the above, a pivot is necessary, but I have sincerely tried to do it but I have not succeeded

SELECT IdFactura,SUM(total) as Total,datepart(day, datediff(day, 0, Fecha)/7 * 7)/7 + 1 as NumerWeek, Nombre from #Docs as pvt
GROUP BY IdFactura,Fecha,Nombre

PIVOT (SUM(pvt.total) FOR pvt.NumerWeek IN ([1],[2],[3],[4],[5],[6])) AS Child
ORDER BY IdFactura

DROP TABLE #Docs

END

I hope you can help me to make this case.

sql sql-server

asked by Rafiña 17.04.2016 в 02:47

source

4 answers

AddSchema command in an XMLReader in Genexus SPA using requirejs and jquery

score 1 · Answer 1

Rafiña this code can help you

select 
    nombre,
    [1] AS 'Semana_1',
    [2] AS 'Semana_2',
    [3] AS 'Semana_3',
    [4] AS 'Semana_4',
    [5] AS 'Semana_5'
from(
    select nombre,NumerWeek,total
    from temp
) as source
pivot
(
    sum(total)
    for NumerWeek in (  
                        [1],
                        [2],
                        [3],
                        [4],
                        [5]
                    )
) as pvt

score 0 · Answer 2

Try with a subquery and this is done by PIVOT

Select IdFactura, Total,NumerWeek,Nombre  from (
SELECT IdFactura,SUM(total) as Total,datepart(day, datediff(day, 0, Fecha)/7 * 7)/7 + 1 as NumerWeek, Nombre from #Docs as pvt
GROUP BY IdFactura,Fecha,Nombre ) Tpivot PIVOT (SUM(pvt.total) FOR pvt.NumerWeek IN ([1],[2],[3],[4],[5],[6])) AS Child
ORDER BY IdFactura

score 0 · Answer 3

What you have to do first is to set the columns dynamically, in your case it would be the number of weeks:

declare @columnas varchar(max)

select @columnas =  coalesce(@columnas + '[' + cast(NumeroSemana as varchar(12)) + '],', '')
FROM (select distinct NumeroSemana from TablaParaPivot) as DTM
set @columnas = left(@columnas,LEN(@columnas)-1)

Once you have the number of columns, you pivot the table:

DECLARE @SQLString NVARCHAR(max)

set @SQLString = 'SELECT * FROM (SELECT NumeroSemana, Total, Nombre as NombreCliente  FROM TablaParaPivot) AS SourceTable
PIVOT
(sum (Total) FOR NumeroSemana IN (' + @columnas + ') ) AS PivotTable'

Finally you execute the query:

EXECUTE sp_executesql @SQLString

I hope I have been of help,

Greetings,

Marta.

score 0 · Answer 4

The best thing for this case is to implement a dynamic pivot in this way:

declare @attr varchar(max)declare @sql varchar(max)set @attr=''
set @sql='
 select 
        Nombre as NombreCliente,
        #attr#
 from 
     ( 
      SELECT
              idfactura, Total, NumerWeek, Nombre
        FROM docs
      ) as source pivot (
            max(Total)
      for NumerWeek in (
                     #attr#
                    )

     ) as pvt
     ' 
select @attr=@attr+'['+cast(v.NumerWeek as varchar)+'],'from (    
    select distinct NumerWeek FROM docs
) as v

set @attr=SUBSTRING(@attr,0,len(@attr))
set @sql=REPLACE(@sql,'#attr#',@attr)
print @sql
exec(@sql)

In this query the structure of the pivot is defined first and then the columns corresponding to each week are dynamically inserted with their values in this way: