none
Consulta SQL 2014

    Pregunta

  • Como puedo hacer para mostrar los datos en horizontal? 

    ID_producto  NombresGenericos

    001               Carro

    001               Troca

    001               Burrito

    ID_producto NombreGenerico1 NombreGenerico2 NombreGenerico3

    001             Carro                    Troca                  Burrito

    Lo que quiero hacer es que por cada Id_Producto solo haya un registro y me separe los nombres genericos por columnas he probado con un pivot pero no me funciona. 

    Muchas gracias.

    miércoles, 10 de abril de 2019 3:45

Respuestas

  • Hola, en efecto como comentaste en tu primer post, necesitas un pivot dinámico, de los datos que muestras se desprenden muchas preguntas como cual es la llave primaria de la tabla ,los datos se pueden repetir?

    con esta query podrías probar, es probable que puedas optimizarla

    CREATE TABLE #pruebas
    (
     ID_producto      VARCHAR(3),
     NombresGenericos  VARCHAR(100)
     )
    
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Carro')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Troca')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Burrito');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Llanero');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('002','Cantinas')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('002','Enchiladas');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('002','Nopalitos');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('003','Rancheros');
    
    DECLARE @cantidadcolumnas AS INT--Se obtiene el máximo de columnas a utilizar para todos los productos
    SET @cantidadcolumnas=(SELECT MAX(t.cantidad)
    FROM
    (
    SELECT COUNT(DISTINCT(NombresGenericos)) AS cantidad FROM #pruebas GROUP BY ID_producto
    )t
    )
    
    DECLARE @enumeradoColumnas AS INT;--Se obtiene el enumerador para las columnas 1,2,3
    WITH cte AS (
    SELECT n=1
    UNION ALL
    SELECT n + 1
    FROM cte
    WHERE n + 1<=@cantidadcolumnas
    )
    SELECT * INTO #t
    FROM cte
    
    DECLARE @columns NVARCHAR(MAX);--Se obtiene el nombre de las columnas NombreGenerico1,NombreGenerico2,etc
    SET @columns = STUFF(
    (
    SELECT ',' +quotename('NombreGenerico' + CAST(n AS VARCHAR(100)))
    FROM #t
    ORDER BY n
    FOR XML PATH('')
    ), 1, 1, '');
    
    DECLARE @sql NVARCHAR(MAX);--Se construye la sentecia 
    SET @sql= N'
    SELECT ID_producto,'+@columns +' '+
    ' FROM
    (
      SELECT ID_producto,NombresGenericos, ''NombreGenerico'' + 
          CAST(row_number() OVER(PARTITION BY ID_producto
                                  ORDER BY ID_producto) AS VARCHAR(10)) SEQ
      from #pruebas
    ) d PIVOT
    (
      MAX(NombresGenericos)
      FOR SEQ IN ('+@columns +')) PIV;'
    
    EXEC sp_executesql @sql;--Se ejecuta el pivot dinámico
    
    DROP TABLE #t--Se borran los temporales
    DROP TABLE #pruebas

    un blog

    Generar PIVOT dinámico en SQL Server

    Qué es ams? Quize decir mas


    Votar y marcar respuestas es agradecer.
    Saludos.
    Lima-Perú



    • Propuesto como respuesta Javi Fernández F viernes, 12 de abril de 2019 4:09
    • Marcado como respuesta Anthonino viernes, 12 de abril de 2019 5:04
    • Editado Augusto1982 viernes, 19 de abril de 2019 4:23
    viernes, 12 de abril de 2019 3:39

Todas las respuestas

  • Hola, necesitas hacer ese pivot solo con los datos que muestras o es una tabla con más registros

     CREATE TABLE #pruebas
    (
     ID_producto      VARCHAR(3),
     NombresGenericos  VARCHAR(100)
     )
    
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Carro')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Troca')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Burrito');
    
    SELECT ID_producto,
    MAX( CASE WHEN NombresGenericos='Carro' THEN 'Carro' ELSE NULL END )     AS [NombreGenerico1],
    MAX( CASE WHEN NombresGenericos='Troca' THEN 'Troca' ELSE NULL END )     AS [NombreGenerico2],
    MAX( CASE WHEN NombresGenericos='Burrito' THEN 'Burrito' ELSE NULL END ) AS [NombreGenerico3]
    FROM #pruebas
    group by ID_producto
    DROP TABLE #pruebas

    Votar y marcar respuestas es agradecer.
    Saludos.
    Lima-Perú


    • Editado Augusto1982 viernes, 12 de abril de 2019 3:51
    miércoles, 10 de abril de 2019 5:45
  • Hola muchas gracias por responder. Esa solución que me diste si funciona para ponerlas en horizontal. Pero en mi caso en el sistema son varios registros y no puedo estar creado un case para cada nombre genérico diferente. A lo que me refiero es que es dinámico. Si me puedes ayudar con eso te lo agradecería mucho, a mi se me ocurre poner una sub consulta en en CASE pero no se como implementarlo. 

    Qué es ams? 

    jueves, 11 de abril de 2019 6:04
  • Hola, en efecto como comentaste en tu primer post, necesitas un pivot dinámico, de los datos que muestras se desprenden muchas preguntas como cual es la llave primaria de la tabla ,los datos se pueden repetir?

    con esta query podrías probar, es probable que puedas optimizarla

    CREATE TABLE #pruebas
    (
     ID_producto      VARCHAR(3),
     NombresGenericos  VARCHAR(100)
     )
    
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Carro')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Troca')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Burrito');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('001','Llanero');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('002','Cantinas')
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('002','Enchiladas');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('002','Nopalitos');
    INSERT INTO #pruebas(ID_producto,NombresGenericos) VALUES('003','Rancheros');
    
    DECLARE @cantidadcolumnas AS INT--Se obtiene el máximo de columnas a utilizar para todos los productos
    SET @cantidadcolumnas=(SELECT MAX(t.cantidad)
    FROM
    (
    SELECT COUNT(DISTINCT(NombresGenericos)) AS cantidad FROM #pruebas GROUP BY ID_producto
    )t
    )
    
    DECLARE @enumeradoColumnas AS INT;--Se obtiene el enumerador para las columnas 1,2,3
    WITH cte AS (
    SELECT n=1
    UNION ALL
    SELECT n + 1
    FROM cte
    WHERE n + 1<=@cantidadcolumnas
    )
    SELECT * INTO #t
    FROM cte
    
    DECLARE @columns NVARCHAR(MAX);--Se obtiene el nombre de las columnas NombreGenerico1,NombreGenerico2,etc
    SET @columns = STUFF(
    (
    SELECT ',' +quotename('NombreGenerico' + CAST(n AS VARCHAR(100)))
    FROM #t
    ORDER BY n
    FOR XML PATH('')
    ), 1, 1, '');
    
    DECLARE @sql NVARCHAR(MAX);--Se construye la sentecia 
    SET @sql= N'
    SELECT ID_producto,'+@columns +' '+
    ' FROM
    (
      SELECT ID_producto,NombresGenericos, ''NombreGenerico'' + 
          CAST(row_number() OVER(PARTITION BY ID_producto
                                  ORDER BY ID_producto) AS VARCHAR(10)) SEQ
      from #pruebas
    ) d PIVOT
    (
      MAX(NombresGenericos)
      FOR SEQ IN ('+@columns +')) PIV;'
    
    EXEC sp_executesql @sql;--Se ejecuta el pivot dinámico
    
    DROP TABLE #t--Se borran los temporales
    DROP TABLE #pruebas

    un blog

    Generar PIVOT dinámico en SQL Server

    Qué es ams? Quize decir mas


    Votar y marcar respuestas es agradecer.
    Saludos.
    Lima-Perú



    • Propuesto como respuesta Javi Fernández F viernes, 12 de abril de 2019 4:09
    • Marcado como respuesta Anthonino viernes, 12 de abril de 2019 5:04
    • Editado Augusto1982 viernes, 19 de abril de 2019 4:23
    viernes, 12 de abril de 2019 3:39
  • Muchas Gracias buen hombre, se nota que usted sabe bastante de estos temas. Tu solución me ayudo con mi problema, muchas gracias.

    Ingeniero

    viernes, 12 de abril de 2019 5:05
  • Hola tu respuesta como ya había comentado si me sirvió y la he adaptado a mi problema. 

    Pero me ha surgido otro problema y espero que me puedas ayudar, el código el el siguiente: 

    CREATE PROC sp_NombresGenericos
    (
    @l varchar(25)
    )
    AS
    BEGIN 
    DECLARE @cantidadcolumnas AS INT--Se obtiene el máximo de columnas a utilizar para todos los productos
    SET @cantidadcolumnas=(SELECT MAX(t.cantidad)
    		FROM
    		(
    			SELECT COUNT(DISTINCT(Nombre_Generico)) AS cantidad FROM Nombres_genericos GROUP BY Id_Producto
    		)t
    	)
    
    DECLARE @enumeradoColumnas AS INT;--Se obtiene el enumerador para las columnas 1,2,3
    		WITH cte AS (
    			SELECT n=1
    				UNION ALL
    			SELECT n + 1
    				FROM cte
    			WHERE n + 1<=@cantidadcolumnas
    		)
    
    SELECT * INTO #t
    FROM cte
    
    DECLARE @columns NVARCHAR(MAX);--Se obtiene el nombre de las columnas NombreGenerico1,NombreGenerico2,etc
    	SET @columns = STUFF(
    	(
    		SELECT ',' +quotename('NombreGenerico' + CAST(n AS VARCHAR(100)))
    		FROM #t
    		ORDER BY n
    		FOR XML PATH('')
    	), 1, 1, '');
    
    DECLARE @sql NVARCHAR(MAX);--Se construye la sentecia 
    
    SET @sql= N'
    SELECT Id_Producto,'+@columns +' '+
    ' FROM
    (
      SELECT n.Id_producto,n.Nombre_Generico, ''NombreGenerico'' + 
          CAST(row_number() OVER(PARTITION BY n.Id_Producto
                                  ORDER BY n.Id_Producto) AS VARCHAR(10)) SEQ
      from Nombres_genericos n
      inner join Productos p
      on n.Id_Producto = p.Id_Producto
      where p.Nombre_Comercial like ' + @l + '%
    ) d PIVOT
    (
      MAX(Nombre_Generico)
      FOR SEQ IN ('+@columns +')) PIV;'
    
    EXEC sp_executesql @sql;--Se ejecuta el pivot dinámico
    
    
    DROP TABLE #t--Se borran los temporales
    END

    Como ves en la consulta final hago un inner join para buscar los nombres genericos por un nombre comercial en especifico que tengo en la tabla productos, el problema esta que cuando ejecuto el sp me manda un error de sintaxis. 

    El sp lo ejecuto asi: exec  sp_NombresGenericos @l = 'A'

    Y me muestra el siguiente error: 

    Agradecería infinitamente tu ayuda.


    Ingeniero

    viernes, 19 de abril de 2019 3:07
  • Hola , esta línea cambiala así

    where p.Nombre_Comercial like ''%'+ @l+'%''


    Votar y marcar respuestas es agradecer.
    Saludos.
    Lima-Perú

    viernes, 19 de abril de 2019 4:20
  • Me sirvio, solo que me ejecuta el sp una infinidad de veces hasta que se satura.

    Ingeniero

    viernes, 19 de abril de 2019 4:54
  • Me sirvio, solo que me ejecuta el sp una infinidad de veces hasta que se satura.

    Ingeniero

    Osea que me retorna el mismo valor varias veces, como que entra en un bucle.

    Ingeniero

    viernes, 19 de abril de 2019 4:55
  • Lo llamo así: 

    exec sp_NombresGenericos @l = 'A'

    Por que quiero que me devuelva todos los productos registrados que empiecen con la letra A


    Ingeniero

    viernes, 19 de abril de 2019 19:36
  • Hola muchas gracias por tu apoyo, ya pude solucionar el problema del bucle infinito, aparentemente sql se había bugeado por que solo tuve que reiniciar la maquina y ya me realizo la consulta bien.

    Ingeniero

    sábado, 20 de abril de 2019 2:10
  • Solo una observacion, los querys dinamicos NO HACEN uso de los indices.

    IIslas Master Consultant SQL Server

    miércoles, 24 de abril de 2019 3:17
  • No comprendo amigo, a que se refiere con eso. Mis disculpas si es algo obvio pero solo soy estudiante aun.


    Ingeniero

    jueves, 25 de abril de 2019 2:44