De filas a columnas

[leon8_correa9]

aqui esta la solucion algo coomplicada pero al fin se pudo, ahora si a dormir un rato

Código SQL:

GO
/******  se crea un  sp_ para cualquier tabla y cualquier dato Objeto:  StoredProcedure [dbo].[GeneratePivotQuery]    Fecha de la secuencia de comandos: 05/28/2013 20:41:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[GeneratePivotQuery] (
@TableName VARCHAR(100), -- Table to select from
@AggregateFunction VARCHAR(10), -- Aggregate to be done on the value column
@ValueColumn VARCHAR(100),
@PivotColumn VARCHAR(100), -- Column for which the values needs to be transposed
@FilterCondition nvarchar(4000), -- Filtering to be done on choosing the pivot values
@OtherColumns VARCHAR(1000)) -- Columns selected other than the pivot columns
AS
BEGIN
 
DECLARE @List VARCHAR(MAX)
DECLARE @ConcatQuery nvarchar(MAX)
 
SET @ConcatQuery = 'select @ListOut = stuff((select distinct '',' +
CHAR(10) + CHAR(9) + '['' + '+ @PivotColumn +
' + '']'' from ' + @TableName +
isnull(' where ' + @FilterCondition,'') +
' for xml path('''')),1,1,'''')'
 
EXEC sp_executesql
@ConcatQuery,
N'@ListOut varchar(MAX) output',
@ListOut = @List OUTPUT
 
/* this will print the query used to generate the pivoted result */
print 'select '+ CHAR(10) + CHAR(9) + @OtherColumns + ','
+ @List + CHAR(10) + ' from ' + @TableName + CHAR(13) +
' pivot (' + CHAR(10) + replicate(CHAR(9),2) +
@AggregateFunction + '(' + @ValueColumn +
') for ' + @PivotColumn + ' in (' +
REPLACE(REPLACE(@List,CHAR(9),''),CHAR(10),'') + ')' +
CHAR(13)+ CHAR(9) + CHAR(9) + ') as P' + replicate(CHAR(10),5)
 
/* this will generate the result set */
EXEC('select '+ @OtherColumns + ',' + @List + ' from ' + @TableName +
' pivot (' + @AggregateFunction + '(' + @ValueColumn +
') for ' + @PivotColumn + ' in (' + @List + ')) as P')
 
END
 

y de esta manera se ejecuta

Código SQL:

DECLARE @TableName VARCHAR(100)
DECLARE @AggregateFunction VARCHAR(10)
DECLARE @ValueColumn VARCHAR(100)
DECLARE @PivotColumn VARCHAR(100)
DECLARE @FilterCondition nvarchar (4000)
DECLARE @OtherColumns VARCHAR(1000)
 
SET @TableName = 'acumulado'
SET @AggregateFunction = 'sum'
SET @ValueColumn = 'subtotal'
SET @PivotColumn = 'FECHA'
SET @FilterCondition = 'FECHA between ''20130401'' and ''20130430'''
SET @OtherColumns = 'sucursal'
 
EXECUTE [dbo].[GeneratePivotQuery]
@TableName
,@AggregateFunction
,@ValueColumn
,@PivotColumn
,@FilterCondition
,@OtherColumns