none
filas como columnas sql dinamico

    Pregunta

  • Hola buen día.

    Tengo un problema al tratar de hacer una consulta cruzada con varias tablas he estado viendo algo de información sobre PIVOT pero me parece algo complicado hacerlo aunque también podre encontrar una salida utilizando código sql dinámico sin utilizar PIVOT.

    Tengo las siguiente estructura de la tabla llamada configuracion:

    Id_Entrada INT

    Id_Evento INT

    Id_Linea INT

    Con esta tabla hago unas operaciones para los registros que se encuentran ahí, de igual manera existen 3 tablas que llevan el nombre sin la palabra Id que son llaves foraneas de la tabla configuracion, ahora bien la tabla configuracion con algunos registros:

    Configuracion

    Id_Entrada Id_Evento Id_Linea

    0 1 1

    1 2 1

    3 1

    3 1 2

    4 2 2

    5 3 2

    Y necesito una consulta que me arroje los siguientes resultados:

    Id_Evento 1(Id_Linea) 2(Id_Linea)

    1 3

    2 1 4

    3 2 5

    He estado intentando hacer subconsultas en la parte de FROM y algunas cosas con los JOIN(LEFT,INNER) pero no logro dar con el resultado esperado, si alguien puede ayudarme lo agradecería mucho.

    Gracias y Saludos!

    miércoles, 18 de julio de 2012 21:59

Respuestas

  • Si el número de valores en la columna [Id_Linea] son conocidos por adelantado, entonces no hace falta usar pivot dinamico.

    Aqui tienes dos queries, el uno usa el operador PIVOT, y el otro usa la forma tradicional para pivotear filas a columnas. Los pasos requeridos para pivotear filas a columnas son:

    - Agrupar (GROUP BY)

    - Regar / extender los valores (CASE)

    - Agregar (MAX / MIN / SUM / ...)

    Estos mismos pasos son usados por el operador pivot:

    - Agrupar (columnas que no participan como parte del operador, por ejemplo [Id_Evento])

    - Regar (clausula FOR [nombre_columna] in (...))

    - Agrupar (MAX, MIN, etc.)

    SET NOCOUNT ON;
    USE tempdb;
    GO
    DECLARE @T TABLE (
    Id_Entrada smallint,
    Id_Evento smallint,
    Id_Linea smallint
    );
    
    INSERT INTO @T (Id_Entrada, Id_Evento, Id_Linea)
    VALUES
    (0, 1, 1),
    (1, 2, 1),
    (2, 3, 1),
    (3, 1, 2),
    (4, 2, 2),
    (5, 3, 2);
    
    SELECT
    	Id_Evento,
    	[1] AS Id_Entrada_Linea_1,
    	[2] AS Id_Entrada_Linea_2
    FROM
    	@T
    	PIVOT
    	(
    	MAX(Id_Entrada)
    	FOR Id_Linea IN ([1], [2])
    	) AS P
    ORDER BY
    	Id_Evento;
    
    SELECT
    	Id_Evento,
    	MAX(CASE WHEN Id_Linea = 1 THEN Id_Entrada END) AS Id_Entrada_Linea_1,
    	MAX(CASE WHEN Id_Linea = 2 THEN Id_Entrada END) AS Id_Entrada_Linea_2
    FROM
    	@T
    GROUP BY
    	Id_Evento
    ORDER BY
    	Id_Evento;
    GO

    Si el número de valores en la columna [Id_Linea] no son conocidos de antemano, entonces si necesitarías usar sql dinamico.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE #T(
    Id_Entrada smallint,
    Id_Evento smallint,
    Id_Linea smallint
    );
    
    INSERT INTO #T (Id_Entrada, Id_Evento, Id_Linea)
    VALUES
    (0, 1, 1),
    (1, 2, 1),
    (2, 3, 1),
    (3, 1, 2),
    (4, 2, 2),
    (5, 3, 2);
    GO
    DECLARE @columns nvarchar(MAX);
    DECLARE @sql nvarchar(MAX);
    
    SET @columns = STUFF(
    (
    SELECT
    	',' + QUOTENAME(Id_Linea)
    FROM
    	(SELECT DISTINCT [Id_Linea] FROM #T) AS T
    ORDER BY
    	[Id_Linea]
    FOR XML PATH('')
    ), 1, 1, '');
    
    SET @sql = N'
    SELECT
    	*
    FROM
    	#T
    	PIVOT
    	(
    	MAX(Id_Entrada)
    	FOR Id_Linea IN (' + @columns + N')
    	) AS P
    ORDER BY
    	Id_Evento;
    ';
    
    EXEC sp_executesql @sql;
    GO
    DROP TABLE #T;
    GO

    Si quieres leer mas sobre esta operacion, entonces te recomiendo la serie de libros de Itzik Ben-Gan, sobre T-SQL.

    Microsoft® SQL Server® 2012 T-SQL Fundamentals
    http://shop.oreilly.com/product/0790145321978.do

    Inside Microsoft® SQL Server® 2008: T-SQL Querying
    http://shop.oreilly.com/product/9780735626034.do


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta Nerk10 jueves, 19 de julio de 2012 15:14
    jueves, 19 de julio de 2012 13:07

Todas las respuestas

  • hola, no veo la necesidad de usar sql dinámico eso generalmente hasta donde yo se se usa cuando el número de columnas debe variar ,

    quizas esto te ayudahi se hace uso del case agrupando con sume

    http://www.elguille.info/net/adonet/firmas_salva_Pivot_Unpivot.htm


    Si la respuesta te fue útil vótala como tal,y si fue respuesta márcala.
    Saludos.
    Lima-Perú



    • Editado Augusto1982 miércoles, 18 de julio de 2012 22:04
    • Marcado como respuesta Nerk10 jueves, 19 de julio de 2012 15:14
    • Desmarcado como respuesta Nerk10 jueves, 19 de julio de 2012 15:16
    • Propuesto como respuesta Augusto C jueves, 19 de julio de 2012 15:37
    • Votado como útil Augusto C jueves, 19 de julio de 2012 15:37
    • Propuesto como respuesta Augusto C jueves, 19 de julio de 2012 15:50
    miércoles, 18 de julio de 2012 22:03
  • Hasta el momento llevo esto, pero no me resuelve del todo mi problema:

    SELECT Eventos.Descripcion,
    (CASE WHEN Lineas.Id_Linea=1 THEN Entradas.Id_Entrada END) AS '1',
    (CASE WHEN Lineas.Id_Linea=2 THEN Entradas.Id_Entrada ELSE 0 END) AS '2'
    FROM tbl_configuracion AS Configuracion LEFT JOIN
    tbl_entrada AS Entradas ON Configuracion.Id_Entrada = Entradas.Id_Entrada INNER JOIN
    tbl_linea AS Lineas ON Configuracion.Id_Linea = Lineas.Id_Linea INNER JOIN
    tbl_evento AS Eventos ON Configuracion.Id_Evento = Eventos.Id_Evento
    GROUP BY Eventos.Id_Evento

    Gracias por tu respuesta.

    miércoles, 18 de julio de 2012 22:53
  • Si el número de valores en la columna [Id_Linea] son conocidos por adelantado, entonces no hace falta usar pivot dinamico.

    Aqui tienes dos queries, el uno usa el operador PIVOT, y el otro usa la forma tradicional para pivotear filas a columnas. Los pasos requeridos para pivotear filas a columnas son:

    - Agrupar (GROUP BY)

    - Regar / extender los valores (CASE)

    - Agregar (MAX / MIN / SUM / ...)

    Estos mismos pasos son usados por el operador pivot:

    - Agrupar (columnas que no participan como parte del operador, por ejemplo [Id_Evento])

    - Regar (clausula FOR [nombre_columna] in (...))

    - Agrupar (MAX, MIN, etc.)

    SET NOCOUNT ON;
    USE tempdb;
    GO
    DECLARE @T TABLE (
    Id_Entrada smallint,
    Id_Evento smallint,
    Id_Linea smallint
    );
    
    INSERT INTO @T (Id_Entrada, Id_Evento, Id_Linea)
    VALUES
    (0, 1, 1),
    (1, 2, 1),
    (2, 3, 1),
    (3, 1, 2),
    (4, 2, 2),
    (5, 3, 2);
    
    SELECT
    	Id_Evento,
    	[1] AS Id_Entrada_Linea_1,
    	[2] AS Id_Entrada_Linea_2
    FROM
    	@T
    	PIVOT
    	(
    	MAX(Id_Entrada)
    	FOR Id_Linea IN ([1], [2])
    	) AS P
    ORDER BY
    	Id_Evento;
    
    SELECT
    	Id_Evento,
    	MAX(CASE WHEN Id_Linea = 1 THEN Id_Entrada END) AS Id_Entrada_Linea_1,
    	MAX(CASE WHEN Id_Linea = 2 THEN Id_Entrada END) AS Id_Entrada_Linea_2
    FROM
    	@T
    GROUP BY
    	Id_Evento
    ORDER BY
    	Id_Evento;
    GO

    Si el número de valores en la columna [Id_Linea] no son conocidos de antemano, entonces si necesitarías usar sql dinamico.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE #T(
    Id_Entrada smallint,
    Id_Evento smallint,
    Id_Linea smallint
    );
    
    INSERT INTO #T (Id_Entrada, Id_Evento, Id_Linea)
    VALUES
    (0, 1, 1),
    (1, 2, 1),
    (2, 3, 1),
    (3, 1, 2),
    (4, 2, 2),
    (5, 3, 2);
    GO
    DECLARE @columns nvarchar(MAX);
    DECLARE @sql nvarchar(MAX);
    
    SET @columns = STUFF(
    (
    SELECT
    	',' + QUOTENAME(Id_Linea)
    FROM
    	(SELECT DISTINCT [Id_Linea] FROM #T) AS T
    ORDER BY
    	[Id_Linea]
    FOR XML PATH('')
    ), 1, 1, '');
    
    SET @sql = N'
    SELECT
    	*
    FROM
    	#T
    	PIVOT
    	(
    	MAX(Id_Entrada)
    	FOR Id_Linea IN (' + @columns + N')
    	) AS P
    ORDER BY
    	Id_Evento;
    ';
    
    EXEC sp_executesql @sql;
    GO
    DROP TABLE #T;
    GO

    Si quieres leer mas sobre esta operacion, entonces te recomiendo la serie de libros de Itzik Ben-Gan, sobre T-SQL.

    Microsoft® SQL Server® 2012 T-SQL Fundamentals
    http://shop.oreilly.com/product/0790145321978.do

    Inside Microsoft® SQL Server® 2008: T-SQL Querying
    http://shop.oreilly.com/product/9780735626034.do


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta Nerk10 jueves, 19 de julio de 2012 15:14
    jueves, 19 de julio de 2012 13:07