Convertir valores de una columna en una cadena con comas

Bravenap · #1 (**permalink**) 16/01/2006, 09:41

Hola a todos. Con el siguiente procedimiento almacenado obtengo datos de varias tablas con subconsultas:

ALTER PROCEDURE dbo.PA_Tipo
(@id int)
AS
SELECT dbo.clasificaciones.ID, dbo.clasificaciones.Nombre, dbo.tipos.Nombre AS Tipo,
(SELECT Nombre FROM nombresVulgares WHERE IDGrupo=@id) AS Sinónimos
FROM dbo.clasificaciones INNER JOIN
dbo.tipos ON dbo.clasificaciones.IDTipo = dbo.tipos.ID
WHERE (dbo.clasificaciones.ID = @id)

La columna que he marcado en rojo puede devolver más de un registro, es decir, que para una misma palabra exista más de un sinónimo. Pues bien, me gustaría que la columna Sinónimos contuviera los valores de la consulta separados por comas, es decir:

ID | Nombre | Tipo | Sinónimos
----------------------------------
1 | Nombre1 | Tipo1 | Sinónimo1a, sinónimo1b, ...

Gracias y un saludo.

Gabriel Sanchez · #2 (**permalink**) 16/01/2006, 12:59

Podrias utilizar una función creada por ti en la cual le pases a la función el @Id y esta te regrese la cadena que deseas.

Checate como crear funciones

-------------------------------------------------

ALTER PROCEDURE dbo.PA_Tipo
(@id int)
AS
SELECT dbo.clasificaciones.ID, dbo.clasificaciones.Nombre, dbo.tipos.Nombre AS Tipo,Buscasinonimos(@id,Resultado)
FROM dbo.clasificaciones INNER JOIN
dbo.tipos ON dbo.clasificaciones.IDTipo = dbo.tipos.ID
WHERE (dbo.clasificaciones.ID = @id)

Bravenap · #3 (**permalink**) 16/01/2006, 13:07

Sí, sí, esa era la idea. Quizás no me expliqué bien. El problema lo tenía en crear la función, ya que acabo de empezar a ver estos temas. La verdad es que SQL me mata, je je.

Gracias y un saludo.

Mithrandir · #4 (**permalink**) 16/01/2006, 16:30

Prueba con algo como:

DECLARE @lista VARCHAR(500)
SET @lista = ''

SELECT @lista = @lista + sinonimo + ', '
WHERE algo = 'algo'

PRINT @lista

Bravenap · #5 (**permalink**) 17/01/2006, 03:42

Eso sería la función ¿no?

Bravenap · #6 (**permalink**) 17/01/2006, 05:14

Veamos, es que estoy un poco liado.

Si creo una función escalar que me devuelva un valor de tipo, por ejemplo, char, bien. Pero este valor lo saco de una consulta SELECT, por lo que la función me da el error de que se está intentando crear una función en linea para una escalar. Por ejemplo, sacado de otra cosa en la que también ando metido y preguntando:

CREATE FUNCTION IDPadre
(@id int)
RETURNS char(50)
AS
DECLARE @retorno char(50)
SET @retorno = (SELECT CONVERT(char(50),IDPadre) FROM categorias WHERE ID = @id)
RETURN @retorno

Lo marcado en rojo devuelve una tabla con una sola columna y un sólo registro. Pero lo que quiero es que el valor de ese campo sea el que se le asigne a la variable @retorno.

Gracias y un saludo.

Gabriel Sanchez · #7 (**permalink**) 17/01/2006, 09:36

-- Te paso un ejemplo:

CREATE FUNCTION Ponceros (@Cad_Ent Varchar(10),@Num Int)
RETURNS Varchar(10)
AS
BEGIN
DECLARE
@Cad_Sal Varchar(10)

Select @Cad_Sal = Right(REPLICATE ('0', @Num)+@Cad_Ent,@Num)

RETURN(@Cad_Sal)
END

-- La función le paso una cadena de un maximo de 10 caracteres y esta
-- función me rellena de ceros a la izquierda
-- select dbo.Ponceros('12',5)

-- Lo unico que se tiene que hacer a esta función es
-- colocarle lo que te esta comentando Mithrandir

Bravenap · #8 (**permalink**) 17/01/2006, 09:43

Gracias Gabriel. No obstante, al cuestión es convertir esa consulta SELECT en una cadena para poder darle un valor a la variable retorno.

Mithrandir · #9 (**permalink**) 17/01/2006, 17:46

Código:

CREATE FUNCTION dbo.fnIDPadre(
	@id int
) RETURNS VARCHAR(500)
AS
BEGIN

DECLARE @lista VARCHAR(500)
SET @lista = ''

SELECT @lista = @lista + sinonimo + ', '
WHERE id = @id

RETURN @lista

END

Bravenap · #10 (**permalink**) 18/01/2006, 01:39

Perdona mi ignorancia Mithrandir pero, ¿qué es sinonimo?¿función, variable, consulta, ...?

Bravenap · #11 (**permalink**) 18/01/2006, 09:32

SQL me mata. Perdonad por tener varios frentes abiertos. Esto es lo que he hecho. Trabaajo con una .adp de Access sonbre SQL.

Código:

ALTER PROCEDURE PA_SubirJerarquia
(@id int)
AS
BEGIN
	DECLARE @IDPadre int
	DECLARE @Nombre char(250)
	DECLARE @tabla TABLE (ColID int, ColNombre char(250))
	SET @IDPadre = (SELECT ID FROM clasificaciones WHERE ID = (SELECT ID FROM FN_IDPadre(@id)))
	SET @Nombre = (SELECT Nombre FROM clasificaciones WHERE ID = (SELECT ID FROM FN_IDPadre(@id)))
	INSERT INTO @tabla VALUES (@IDPadre, @Nombre) 
	IF @Nombre IS NULL
		SELECT 'No hay registros'
	ELSE
		SELECT * FROM @tabla
END

Y la función FN_IDPadre es:

Código:

ALTER FUNCTION dbo.FN_IDPadre
(@id int)
RETURNS TABLE
AS
RETURN (SELECT ID FROM clasificaciones WHERE ID = (SELECT IDPadre FROM clasificaciones WHERE ID = @id) )

Sería más fácil con una función escalar pero no me sale. Bueno, el caso es que cuando ejecuto el procedimiento y de parámetro le paso el 8 me dice que se ha ejecutado todo bien pero que no se devuelven resultados (ojo, el condicional no se llega a ejecutar). Sin embargo, con el siguiente código sin procedimientos almacenados sí me devuelve un registro correctamente:

Código:

DECLARE @Nombre char(250)
SET @Nombre =(SELECT Nombre FROM clasificaciones WHERE ID = (SELECT ID FROM FN_IDPadre(8)))
IF @Nombre IS NULL
SELECT 'No pertenece a ningún grupo superior' AS "Nombre"
ELSE
SELECT @Nombre AS "Nombre"

¿no se supone que es lo mismo?

Bravenap · #12 (**permalink**) 18/01/2006, 10:45

Olvídense de lo anterior. Con Web Data Administrator sí que me devuelve registros. Bueno, me voy a cercando al resultado final. Este es el código:

Código:

ALTER PROCEDURE PA_SubirJerarquia
(@id int)
AS
BEGIN
	DECLARE @IDPadre int
	DECLARE @Nombre char(250)
	DECLARE @tabla TABLE (ColID int, ColNombre char(250))
	SET @IDPadre = (SELECT ID FROM clasificaciones WHERE ID = (SELECT ID FROM FN_IDPadre(@id)))
	SET @Nombre = (SELECT Nombre FROM clasificaciones WHERE ID = (SELECT ID FROM FN_IDPadre(@id)))
	INSERT INTO @tabla VALUES (@IDPadre, @Nombre) 
	IF @Nombre IS NULL
		BEGIN
			-- No hay registros
			RETURN
		END
	ELSE
		BEGIN
			EXEC PA_SubirJerarquia @IDPadre
			SELECT * FROM @tabla
		END
END

¿Qué obtengo? Pues lógicamente una tabla por cada escalón que asciende en la jerarquía con un sólo registro. Lo suyo sería un registro por cada escalón en una sola tabla y por eso probé a cambiar por esto:

Código:

INSERT INTO @tabla VALUES (SELECT * FROM (EXEC PA_SubirJerarquia @IDPadre))

Pero errores

Mithrandir · #13 (**permalink**) 18/01/2006, 16:08

Creo que te complicas demasiado. Una función del tipo que coloqué debe de funcionar para mostrar una lista creada en base a registros.

Código:

PRINT 'La lista de todos los registros resumida en una sola cadena para id 25 es: ' + dbo.fnIdPadre(25)

Si no es eso entonces no entiendo que es lo que necesitas.

PD. No entendi lo de los sinonimos

Bravenap · #14 (**permalink**) 19/01/2006, 02:13

¿Ehhh? sí, claro. Es que estoy liando varias cosas.

Por un lado, lo último que puse voy a ver si lo meto en otro tema que tengo abierto en este foro.

Por otro, la función que me pusiste tiene esto:

Código:

SELECT @lista = @lista + sinonimo + ', '
WHERE id = @id

¿Qué es eso que llamas sinonimo?¿y sobre que tabla hace la consulta?

Mithrandir · #15 (**permalink**) 19/01/2006, 17:43

Ahh, si. Lo tomé de tu pregunta. La tabla sería NombresVulgares y el campo es Nombre (según entiendo de tu pregunta inicial)

Bravenap · #16 (**permalink**) 20/01/2006, 03:04

Bien, vamos encauzando las cosas. Primero pongo el procedimiento:

Código:

CREATE PROCEDURE dbo.PA_Grupo (@id int)
AS
BEGIN
	SELECT
		dbo.clasificaciones.ID,
		dbo.clasificaciones.Nombre,
		dbo.tipos.Nombre AS Tipo,
		dbo.FN_BuscaVulgares(@id) AS "Vulgares"
		FROM dbo.clasificaciones
	INNER JOIN
            	dbo.tipos ON dbo.clasificaciones.IDTipo = dbo.tipos.ID
	WHERE (dbo.clasificaciones.ID = @id)
END

y ahora la función:

Código:

CREATE FUNCTION dbo.FN_BuscaVulgares
(@id int)
RETURNS char(500)
AS
BEGIN
	DECLARE @retorno char(500)
	SET @retorno = ''
	SELECT @retorno =  @retorno + ....
             -- Esta consulta puede devolver varios valores y, de hecho lo hace
             --(SELECT Nombre FROM dbo.nombresVulgares WHERE (IDGrupo = @id))
             --Por tanto deberían ser estos valores los que se unieran con comas
	RETURN @retorno -- Devuelve una cadena a base de valores y comas
END

Mithrandir · #17 (**permalink**) 23/01/2006, 12:57

Exactamente, a eso me refiero.

Bravenap · #18 (**permalink**) 24/01/2006, 05:37

Bueno, casi casi tengo terminado el procedimiento. Pero se me sigue resistiendo la función y, lo siento Mithrandir, pero tu última respuesta me ha dejado perplejo.

Te vuelvo a poner la función y te remarco el problema:

Código:

CREATE FUNCTION dbo.FN_BuscaVulgares
(@id int)
RETURNS char(500)
AS
BEGIN
	DECLARE @retorno char(500)
	SET @retorno = ''
	SELECT @retorno =  @retorno + ....
             -- Esta consulta puede devolver varios valores y, de hecho lo hace
             --(SELECT Nombre FROM dbo.nombresVulgares WHERE (IDGrupo = @id))
             --Por tanto deberían ser estos valores los que se unieran con comas
	RETURN @retorno -- Devuelve una cadena a base de valores y comas
END

Bravenap · #19 (**permalink**) 24/01/2006, 07:22

Ahora ya sí que sí que casi está, eso sí, he tenido que recurrir a un cursor:

Código:

ALTER FUNCTION dbo.FN_BuscaSinonimos
(@id int)
RETURNS varchar(500)
AS
BEGIN
	DECLARE @retorno varchar(500), @Nombre varchar(250)
	SET @retorno = ''
	IF EXISTS (SELECT Nombre FROM clasificaciones WHERE SinonimoDe = @id)
		BEGIN
			DECLARE curs cursor FOR
				SELECT Nombre FROM clasificaciones WHERE SinonimoDe = @id
			OPEN curs
			FETCH NEXT FROM curs	INTO @Nombre
			WHILE  @@FETCH_STATUS = 0
			BEGIN
				IF @retorno = ''
					BEGIN
						SELECT @retorno = @retorno + @Nombre
					END
				ELSE
					BEGIN
						SELECT @retorno =  @retorno + ', ' + @Nombre
					END
				FETCH NEXT FROM curs	INTO @Nombre
			END
			CLOSE curs
			DEALLOCATE curs
			SET @retorno = 'Sinónimos de '+(SELECT Nombre FROM clasificaciones WHERE ID = @id)+': '+@retorno
		END
	ELSE
		SET @retorno = 'No hay sinónimos de '+(SELECT Nombre FROM clasificaciones WHERE ID = @id)
	RETURN @retorno
END

Bien, pero para SELECT dbo.FN_BuscaSinonimos(24) devuelve esto:

Cita:

Sinónimos de Gnetopsida : Gnetatae

Cuando debería devolver:

Cita:

Sinónimos de Gnetopsida : Gnetatae, Pepe

Y es seguro porque esto:
'SELECT COUNT(*) FROM clasificaciones WHERE SinonimoDe = 24' da 2

Bravenap · #20 (**permalink**) 24/01/2006, 08:02

Ya está, ya está....

El problema estaba en la longitud de las variables varchar(500), por lo que me colocaba espacios en blanco al final de cada asignación. Le he colocado a todo lo habido y por haber el RTRIM y punto pelota, ya sale tó.

Muchas gracias y un saludo.

Mithrandir · #21 (**permalink**) 24/01/2006, 15:02

El select del tipo select @var = @var + campo evita que uses el cursor, además de que el código es mucho más compacto.

Lo bueno es que ya te funciona, pero si te interesa optimizarlo prueba la función como la describo arriba.

Bravenap · #22 (**permalink**) 25/01/2006, 02:31

Mithrandir, entiendo que es preferible no usar cursores pero entonces ¿cómo se recorren las filas de una consulta para ir concactenando valores?

Entiendo, por lo que me comentas, que debería ser algo así:

Código:

ALTER FUNCTION dbo.FN_BuscaSinonimos2
(@id int)
RETURNS varchar(500)
AS
BEGIN
	DECLARE @retorno varchar(500)
	SET @retorno = ''
	IF EXISTS (SELECT Nombre FROM clasificaciones WHERE SinonimoDe = @id)
		BEGIN
			IF @retorno = ''
				BEGIN
					SELECT @retorno = RTRIM(@retorno) +'= '+Nombre
				END
			ELSE
				BEGIN
					SELECT @retorno =  RTRIM(@retorno) + ', = ' + Nombre
				END
		END
	RETURN @retorno
END

el nombre de la columna 'Nombre' no es válido. Y si pongo algo así:

Código:

ALTER FUNCTION dbo.FN_BuscaSinonimos2
(@id int)
RETURNS varchar(500)
AS
BEGIN
	DECLARE @retorno varchar(500), @NombreSin char(250)
	SET @retorno = ''
	IF EXISTS (SELECT Nombre FROM clasificaciones WHERE SinonimoDe = @id)
		BEGIN
			SELECT @NombreSin = (SELECT Nombre FROM clasificaciones WHERE SinonimoDe = @id)
			IF @retorno = ''
				BEGIN
					SELECT @retorno = RTRIM(@retorno) +'= '+ RTRIM(@NombreSin)
				END
			ELSE
				BEGIN
					SELECT @retorno =  RTRIM(@retorno) + ', = ' + RTRIM(@NombreSin)
				END
		END
	RETURN @retorno
END

"La subconsulta ha devuelto más de un valor, lo que no es correcto cuando va a continuación de =, !=, <, <=, >, >= o cuando se utiliza como expresión."

Mithrandir · #23 (**permalink**) 25/01/2006, 15:28

Código:

CREATE FUNCTION dbo.FN_BuscaSinonimos2(
	@id int
) RETURNS NVARCHAR(500)
AS
BEGIN

DECLARE @sinonimos NVARCHAR(500)
SET @sinonimos = ''

SELECT @sinonimos = @sinonimos + nombre + ', '
FROM clasificaciones
WHERE id = @id

RETURN @sinonimos 

END

El "@var = @var + ..." es el que hace la "magia" y evita el cursor

Bravenap · #24 (**permalink**) 26/01/2006, 02:12

Gracias por tu paciencia Mithrandir. Aún no lo he probado, pero me temo que me va a salir un error: No se ha definido el nombre "nombre".

Código:

SELECT @sinonimos = @sinonimos + nombre + ', '

Gracias y un saludo.

Mithrandir · #25 (**permalink**) 26/01/2006, 16:44

¿Entonces de donde sacas esto?

Código:

....
IF EXISTS (SELECT Nombre FROM clasificaciones WHERE SinonimoDe = @id)
		BEGIN
			SELECT @NombreSin = (SELECT Nombre FROM cl....

Ajusta las sentencias a los nombres de campos y de tablas que tienes.

Bravenap · #26 (**permalink**) 31/01/2006, 01:58

Yujuuuuu. Por fin. Me costó entenderlo. Estaba interpretando mal dónde poner la sentencia de consulta, de modo que buscaba "Nombre" sin poner la tabla. Bueno, pues este es el resultado:

Código:

ALTER FUNCTION dbo.FN_BuscaSinonimos
(@id int)
RETURNS varchar(500)
AS
BEGIN
	DECLARE @retorno varchar(500)
	SET @retorno = ''
	IF EXISTS (SELECT Nombre FROM clasificaciones WHERE SinonimoDe = @id)
		BEGIN
			SELECT @retorno = @retorno + '= ' + Nombre + ', ' FROM clasificaciones WHERE SinonimoDe = @id
		END
	RETURN @retorno 
END

Muchísimas gracias Mithrandir. Ya voy comprendiendo un poco más esto del T-SQL.

Un saludo.

Mithrandir · #27 (**permalink**) 31/01/2006, 13:14

Excelente, que bueno que ha quedado

Bravenap · #28 (**permalink**) 01/02/2006, 01:39

Je je, gracias de nuevo. Por cierto, he hecho una pequeña corrección, que aunque se puede resolver con un condicional previo me parecía menos tedioso hacerlo así:

Código:

......

SET @retorno = SUBSTRING(@retorno,0,LEN(@retorno))
RETURN @retorno

Así te quitas de en medio la coma que queda al final sin que haya más sinónimos detrás.

Un saludo.