sql-server-2005 - transponer - pivot sql w3schools

Pivotes con columnas dinámicas en SQL Server

Question

Estoy trabajando en una consulta SQL usando pvots con columnas dinámicas en SQL Server (T-sql). En lugar de enviar mi larga consulta, estoy ilustrando mi problema con un modelo simplificado.

Creo 2 tablas: Table1 y Table2 y las rellene con algunas entradas de la siguiente manera:

Tabla 1:

Col_ID1 ............... Col_Name

1 ......................... Ene-11

2 ......................... Feb-11

3 ......................... Mar-11

Tabla 2:

Col_ID2 ...... Cuenta ..... Nombre de cuenta ...... Cantidad

1 ............... 121 ........... Electricidad ............ 10000

2 ............... 121 ........... Electricidad ............ 20000

3 ............... 121 ........... Electricidad ............ 30000

1 ............... 122 ........... Teléfono .............. 100

2 ............... 122 ........... Teléfono .............. 200

3 ............... 122 ........... Teléfono .............. 300

Estoy creando un Pivot, pero quiero que los nombres de las columnas se generen de forma paramétrica (en función de las fechas introducidas desde la pantalla de entrada) y no codificadas.

La siguiente consulta funciona bien, pero solo da unas pocas columnas:

Ene-11 ........... Feb-11 ........... Mar-11

10,000.00 ...... 20,000.00 ...... 30,000.00

100.00 ............... 200.00 ........... 300.00

Quiero que la consulta devuelva las columnas descriptivas también, como sigue:

Cuenta ........... Nombre de cuenta ........... Ene-11 ............ Feb-11 ........ ...... Mar-11

121 ................. Electricidad .................. 10,000.00 ...... 20,000.00 ..... ..... 30,000.00

122 ................. Teléfono ..................... 100.00 ......... ..200.00 ............. 300.00

¿Podría alguien ayudarme a modificar mi consulta para poder alcanzar mi objetivo?

Esta consulta es una adaptación del siguiente artículo escrito por el Dr. Andras en septiembre de 2007. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Alguien comentó que el código podría estar sujeto a ataques de inyección y propuso usar la función Quotename en lugar de concatenar corchetes.

¿Podría explicar cómo usar Quotename en mi consulta?

Muchas gracias,

Leon Lai.
.
.

Aquí está mi consulta:

------------------------ crear y poblar table1 ---------------------- ----------

CREATE TABLE Table1
(Col_ID1 INT,
Col_Name varchar(10))
INSERT INTO Table1 VALUES (1, ''Jan-11'')
INSERT INTO Table1 VALUES (2, ''Feb-11'')
INSERT INTO Table1 VALUES (3, ''Mar-11'') 

------------------------- create & populate table2 --------------------- -------------

CREATE TABLE Table2
(Col_ID2 INT,
Account varchar(10),
AccountName varchar(20),
Amount numeric(18,6))
INSERT INTO Table2 VALUES (1, 121, ''Electricity'', 10000)
INSERT INTO Table2 VALUES (2, 121, ''Electricity'', 20000)
INSERT INTO Table2 VALUES (3, 121, ''Electricity'', 30000)
INSERT INTO Table2 VALUES (1, 122, ''Telephone'', 100)
INSERT INTO Table2 VALUES (2, 122, ''Telephone'', 200)
INSERT INTO Table2 VALUES (3, 122, ''Telephone'', 300) 

---------------------------------- crear encabezados de columnas ------------- ------

DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
''],['' + t2.Col_Name
FROM Table1 AS t2
ORDER BY ''],['' + t2.Col_Name
FOR XML PATH('''')
), 1, 2, '''') + '']'' 

------------------------------------- create @query ---------- ------------

DECLARE @query NVARCHAR(4000)
SET @query = N''SELECT ''+
@cols +''
FROM 

-------------------------- subconsulta -----

(SELECT  t1.Col_Name,  t2.Account,  t2.Amount  FROM Table1 AS t1  JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2  ) p

-------------------- pivote -------------------------

PIVOT  (  Sum ([Amount] )  FOR Col_Name IN  ( ''+  @cols +'' )  ) AS pvt ''

---------------------- exec & drop ----------

EXECUTE(@query)
drop table table1
drop table table2

=============================================== =====

Hola Felipe,

Muchas gracias por tu respuesta.

Su consulta propuesta funciona sin problemas y genera la pantalla esperada, pero no es exactamente lo que yo quería.

Primero, gracias por el código: SELECT @cols = isnull (@cols + '','', '''') + ''['' + Col_Name + '']''

Es más simple y reemplaza mi línea que implica cosas y ruta xml, aparentemente con el mismo efecto.

Déjame explicarte lo que quiero hacer.

Deseo desarrollar una consulta en Sap Business 1 (Un paquete de contabilidad, o llámalo ERP). Sap usa T-sql en Microsoft Server 2008, y tiene su propio generador de consultas. Con muy pocas excepciones, Sap sql es similar a T-sql.

Quiero que mi consulta brinde una lista de todos los ingresos y gastos mes a mes durante un período de 12 meses.

Sin embargo, no quiero que mis encabezados de columna estén codificados de manera rígida (ya que esto requeriría que modifique mi consulta de vez en cuando) de la siguiente manera:

Ene-11, Feb-11, Mar-11, Abr-11, ..... Dic-11

Más bien, quiero que los encabezados de las columnas se generen dinámicamente a partir de las fechas que el usuario ingresa en la pantalla de entrada.

Como mencioné, la consulta que publiqué en el foro es una versión demasiado simplificada de mi consulta real, que se usa solo para ilustración. La consulta real contiene varias variables y una pantalla de entrada (llamada Query - Cuadro de Criterios de Selección en Sap b1) permite al usuario ingresar una fecha. Es esta fecha la que se usará para determinar dinámicamente los nombres de las columnas.

Es por eso que necesitaba herramientas tan complicadas como @cols, @query, pivot, etc.

Si ingreso, digamos ''01 .06.11 ''(1 de junio de 2011) en la pantalla de entrada, esta fecha se transmitirá al SQL que determinará los nombres de los encabezados de las columnas como sigue:

11-jun, 11-jul., 11-ago ..... May-12.

Si ingreso otra fecha, digamos ''01 .09.10 ''(01 Sep 2010), los encabezados de las columnas cambiarán a:

Sep-10, Oct-10, .... Aug-11

Parece que has codificado mis encabezados de columna.

¿Podría echar un segundo vistazo a mi consulta y proponer algo que permita que los nombres de las columnas se generen de forma paramétrica en lugar de ser codificados?

Gracias

Leon Lai

Answer 1

Agregar esas columnas es muy simple. La consulta final sería

SELECT Account, AccountName, [Feb-11],[Jan-11],[Mar-11] FROM
(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p
PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( [Feb-11],[Jan-11],[Mar-11] )
) AS pvt 

que tiene t2.AccountName agregado a la subconsulta, y Account y AccountName agregados a la SELECT inicial. Mezcle en la declaración de compilación y listo:

DECLARE @query NVARCHAR(4000)
SET @query = N''SELECT Account, AccountName, '' + @cols +'' FROM
(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p
PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( ''+
@cols +'' )
) AS pvt '' 

En cuanto a la inyección de SQL, la única forma en que puedo ver que eso ocurra es si alguien integra de algún modo código malicioso dentro de Table1.Col_Name, y si tiene que preocuparse por eso, tiene problemas mayores que "bloquear" esta consulta dinámica.

También vale la pena mencionar que utilizaría lo siguiente para compilar la lista de columnas (@Cols) porque es más corta y más fácil de leer, pero sobre todo porque no me gusta XML.

DECLARE @cols NVARCHAR(2000)
SELECT @cols = isnull(@cols + '','', '''') + ''['' + Col_Name + '']''
FROM Table1
ORDER BY Col_Name

Answer 2

Agregando otra respuesta, ya que esta la edita casi una segunda pregunta. (Sin detalles y detalles, solo puedo ofrecer esquemas generales y código de psuedo; no conozco SAP).

Comencemos con el pivote. Necesita generar columnas etiquetadas por, presumiblemente, mes, que tenía en su ejemplo como Table1.Col_Name, varchar (10); esos valores se extraen y se agregan dinámicamente a la consulta dinámica como nombres de columna. Si no existe dicha columna en la base de datos, entonces debe construirla para la consulta en función de los datos ingresados ​​por el usuario. Trabajaré con las siguientes suposiciones: - Los datos tienen una columna de fecha y hora, donde se puede encontrar cualquier valor (año a milisegundo): el usuario especifica una "fecha de inicio" (¿es siempre el primero de un mes?) Y usted tiene que generar columnas para eso y los siguientes 11 meses, agregando los datos que caen dentro de cada mes objetivo.

Paso 1, configuraría y llenaría una tabla temporal que contiene las 12 columnas de destino:

CREATE TABLE #Months
(
Col_Name varchar(10)
,MonthStart datetime
,MonthEnd datetime
)

La etiqueta está formateada como desea que aparezca, MonthStart sería el comienzo absoluto del mes (por ejemplo, 1 de octubre de 2011 00: 00: 00,000), y MonthEnd sería el comienzo absoluto del próximo mes (1 de noviembre de 2011 00) : 00: 00.000) - esto le permite usar SELECT … from <table> where DataDate >= MontStart and DataDate < MonthEnd para obtener todos los datos dentro de ese mes.

A continuación, únase a esta tabla en su tabla de datos y agregue, algo así como:

SELECT
mt.Col_Name
,sum(dt.RawData) Amount
from #Months mt
inner join MyData dt
on dt.DataDate >= mt.MonthStart
and dt.DataDate < mt.MonthEnd -- Yes, ON clauses don''t have to be simple equivalencies!
inner join <other tables as necessary for Account, AccountName, etc.>

Enchúfalo como la consulta más interna de la declaración dinámica, extrae / compila la lista de Col_Names de la tabla temporal utilizando la consulta que no es XML (no sé cómo llamarlo), compila y ejecuta dinámicamente, y debes sé bueno.