Código SQL:
Ver originalCREATE TABLE tabla
(
region INT,
estado VARCHAR(20)
)
INSERT INTO tabla VALUES (2,'Baja California')
INSERT INTO tabla VALUES (2,'Sonora')
INSERT INTO tabla VALUES (2,'Monterrey')
INSERT INTO tabla VALUES (2,'Cohahuila')
INSERT INTO tabla VALUES (1,'Nayarit')
INSERT INTO tabla VALUES (1,'Jalisco')
INSERT INTO tabla VALUES (1,'Michoacan')
INSERT INTO tabla VALUES (1,'Puebla')
DECLARE @groups AS VARCHAR(MAX)
SELECT @groups=COALESCE(@groups + ',', '') + estado FROM tabla WHERE region=1
print @groups
CREATE FUNCTION fn_return_regions(@region INT)
RETURNS VARCHAR(MAX) WITH schemabinding
AS
BEGIN
DECLARE @groups AS VARCHAR(MAX)
SELECT @groups=COALESCE(@groups + ',', '') + estado FROM #temp WHERE region=@region
RETURN @groups
END
GO
SELECT region,fn_return_regions(region) FROM tabla GROUP BY region
saludos!