Consulta SQL 2014

Question

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.

Answer 1

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ú

Answer 2

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? 

Answer 3

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ú

Answer 4

Muchas Gracias buen hombre, se nota que usted sabe bastante de estos temas. Tu solución me ayudo con mi problema, muchas gracias.

---

Ingeniero

Answer 5

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

Answer 6

Hola , esta línea cambiala así

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

---

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

Answer 7

Me sirvio, solo que me ejecuta el sp una infinidad de veces hasta que se satura.

---

Ingeniero

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

Solo una observacion, los querys dinamicos NO HACEN uso de los indices.

---

IIslas Master Consultant SQL Server

Answer 12

No comprendo amigo, a que se refiere con eso. Mis disculpas si es algo obvio pero solo soy estudiante aun.

---

Ingeniero