none
problema con instruccion SQL RRS feed

  • Pregunta

  • tengo la siguiente lógica en una tabla:

    y la quiero que me muestre los datos en este formato

    ¿Como lo podría hacer usando sólo sql?

    Atento a sus comentarios


    Francisco Birrer
    Arquitecto de Software e-Process

    lunes, 24 de octubre de 2016 20:46

Respuestas

  • Esta operacion se conoce como PIVOT, donde se transponen las filas a columnas. La manera comun de hacerlo en T-SQL es:

    - Agrupar (GROUP BY)

    - Regar valores (expresion CASE)

    - Agregar (MIN / MAX / COUNT / etc.)

    La clave de lograr esto de forma dinamica es saber cual es el maximo numero de porcentajes para poder componer los diferentes conjuntos de pares (porcentaje, valor).

    SELECT TOP (1) COUNT(*) AS cnt FROM T GROUP BY codigo ORDER BY cnt DESC;

    Con esta idea podemos usar una tabla auxiliar de numeros para armar los pares.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums](@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
    GO
    SET NOCOUNT ON;
    GO
    CREATE TABLE #T (
    codigo int,
    porcentaje int,
    valor int
    );
    GO
    INSERT INTO #T
    	(codigo, porcentaje, valor)
    VALUES
    	(1, 10, 100),
    	(1, 15, 150),
    	(2, 10, 100),
    	(2, 15, 150),
    	(3, 15, 150);
    GO
    -- forma estatica, sabiendo que existen dos pares (p, v)
    WITH R AS (
    SELECT
        codigo,
        porcentaje,
        valor,
        ROW_NUMBER() OVER(PARTITION BY codigo ORDER BY porcentaje) AS rn
    FROM
    	#T
    )
    SELECT
        codigo,
        MAX(CASE WHEN rn = 1 THEN porcentaje END) AS porcentaje_1,
        MAX(CASE WHEN rn = 1 THEN valor END) AS valor_1,
        MAX(CASE WHEN rn = 2 THEN porcentaje END) AS porcentaje_2,
        MAX(CASE WHEN rn = 2 THEN valor END) AS valor_2
    FROM
        R
    GROUP BY
        codigo
    ORDER BY
        codigo;
    GO
    -- forma dinamica, calculando el numero de pares (p, v) y armando las columnas
    DECLARE
    	@columns AS nvarchar(MAX),
    	@sql nvarchar(MAX);
    
    SET @columns = (
    SELECT
    	',' + 'MAX(CASE WHEN rn = ' + CAST(n AS varchar(10)) + ' THEN porcentaje END) AS porcentaje_' + CAST(n AS varchar(10)) + 
    	',' + 'MAX(CASE WHEN rn = ' + CAST(n AS varchar(10)) + ' THEN valor END) AS valor_' + CAST(n AS varchar(10))
    FROM
    	dbo.ufn_GetNums(1, (SELECT TOP (1) COUNT(*) AS cnt FROM #T GROUP BY codigo ORDER BY cnt DESC)) AS T
    ORDER BY
    	n
    FOR XML PATH('')
    );
    
    SET @sql = N'
    WITH R AS (
    SELECT
        codigo,
        porcentaje,
        valor,
        ROW_NUMBER() OVER(
        PARTITION BY codigo 
        ORDER BY porcentaje
        ) AS rn
    FROM
        #T
    )
    SELECT codigo' + @columns + N' FROM R GROUP BY codigo ORDER BY codigo;';
    
    PRINT @sql;
    
    EXECUTE sys.sp_executesql @sql;
    GO
    DROP TABLE #T;
    GO
    


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 25 de octubre de 2016 15:18

Todas las respuestas

  • Francisco Birrer,

    ¿Es posible cambiar el formato a algo como?

    CODIGO  |   10    |      15     |      20    |

    Nota que se define las cabeceras según los "n" porcentajes y se agregan los valores según el porcentaje:

    DECLARE @Columnas nvarchar(MAX);
    DECLARE @CadenaSQL nvarchar(MAX);	
    
    SET @Columnas = 
        STUFF(( SELECT N',' + CONVERT(nvarchar(10), QUOTENAME(t1.PORCENTAJE)) FROM NombreTabla t1 
        GROUP BY t1.PORCENTAJE FOR XML PATH (''), TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, N'');
    
    SET @CadenaSQL = N'WITH PivotInput AS (
    		SELECT CODIGO, PORCENTAJE, VALOR FROM NombreTabla)
    		SELECT * FROM PivotInput
    		PIVOT (MAX(VALOR) FOR PORCENTAJE IN (' + @Columnas + N')) AS PivotTable;';
    
    EXECUTE sp_executesql @CadenaSQL;


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

    lunes, 24 de octubre de 2016 22:37
  • Esta operacion se conoce como PIVOT, donde se transponen las filas a columnas. La manera comun de hacerlo en T-SQL es:

    - Agrupar (GROUP BY)

    - Regar valores (expresion CASE)

    - Agregar (MIN / MAX / COUNT / etc.)

    La clave de lograr esto de forma dinamica es saber cual es el maximo numero de porcentajes para poder componer los diferentes conjuntos de pares (porcentaje, valor).

    SELECT TOP (1) COUNT(*) AS cnt FROM T GROUP BY codigo ORDER BY cnt DESC;

    Con esta idea podemos usar una tabla auxiliar de numeros para armar los pares.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums](@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
    GO
    SET NOCOUNT ON;
    GO
    CREATE TABLE #T (
    codigo int,
    porcentaje int,
    valor int
    );
    GO
    INSERT INTO #T
    	(codigo, porcentaje, valor)
    VALUES
    	(1, 10, 100),
    	(1, 15, 150),
    	(2, 10, 100),
    	(2, 15, 150),
    	(3, 15, 150);
    GO
    -- forma estatica, sabiendo que existen dos pares (p, v)
    WITH R AS (
    SELECT
        codigo,
        porcentaje,
        valor,
        ROW_NUMBER() OVER(PARTITION BY codigo ORDER BY porcentaje) AS rn
    FROM
    	#T
    )
    SELECT
        codigo,
        MAX(CASE WHEN rn = 1 THEN porcentaje END) AS porcentaje_1,
        MAX(CASE WHEN rn = 1 THEN valor END) AS valor_1,
        MAX(CASE WHEN rn = 2 THEN porcentaje END) AS porcentaje_2,
        MAX(CASE WHEN rn = 2 THEN valor END) AS valor_2
    FROM
        R
    GROUP BY
        codigo
    ORDER BY
        codigo;
    GO
    -- forma dinamica, calculando el numero de pares (p, v) y armando las columnas
    DECLARE
    	@columns AS nvarchar(MAX),
    	@sql nvarchar(MAX);
    
    SET @columns = (
    SELECT
    	',' + 'MAX(CASE WHEN rn = ' + CAST(n AS varchar(10)) + ' THEN porcentaje END) AS porcentaje_' + CAST(n AS varchar(10)) + 
    	',' + 'MAX(CASE WHEN rn = ' + CAST(n AS varchar(10)) + ' THEN valor END) AS valor_' + CAST(n AS varchar(10))
    FROM
    	dbo.ufn_GetNums(1, (SELECT TOP (1) COUNT(*) AS cnt FROM #T GROUP BY codigo ORDER BY cnt DESC)) AS T
    ORDER BY
    	n
    FOR XML PATH('')
    );
    
    SET @sql = N'
    WITH R AS (
    SELECT
        codigo,
        porcentaje,
        valor,
        ROW_NUMBER() OVER(
        PARTITION BY codigo 
        ORDER BY porcentaje
        ) AS rn
    FROM
        #T
    )
    SELECT codigo' + @columns + N' FROM R GROUP BY codigo ORDER BY codigo;';
    
    PRINT @sql;
    
    EXECUTE sys.sp_executesql @sql;
    GO
    DROP TABLE #T;
    GO
    


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 25 de octubre de 2016 15:18