Solución:
Idealmente, su mejor solución sería normalizar Table2 para no almacenar una lista separada por comas.
Una vez que tenga estos datos normalizados, podrá consultarlos fácilmente. La nueva estructura de la tabla podría ser similar a esta:
CREATE TABLE T1
(
[col1] varchar(2),
[col2] varchar(5),
constraint pk1_t1 primary key (col1)
);
INSERT INTO T1
([col1], [col2])
VALUES
('C1', 'john'),
('C2', 'alex'),
('C3', 'piers'),
('C4', 'sara')
;
CREATE TABLE T2
(
[col1] varchar(2),
[col2] varchar(2),
constraint pk1_t2 primary key (col1, col2),
constraint fk1_col2 foreign key (col2) references t1 (col1)
);
INSERT INTO T2
([col1], [col2])
VALUES
('R1', 'C1'),
('R1', 'C2'),
('R1', 'C4'),
('R2', 'C3'),
('R2', 'C4'),
('R3', 'C1'),
('R3', 'C4')
;
La normalización de las tablas le facilitaría mucho la consulta de los datos al unir las tablas:
select t2.col1, t1.col2
from t2
inner join t1
on t2.col2 = t1.col1
Ver demostración
Luego, si desea mostrar los datos como una lista separada por comas, puede usar FOR XML PATH
y STUFF
:
select distinct t2.col1,
STUFF(
(SELECT distinct ', ' + t1.col2
FROM t1
inner join t2 t
on t1.col1 = t.col2
where t2.col1 = t.col1
FOR XML PATH ('')), 1, 1, '') col2
from t2;
Ver demostración.
Si no puede normalizar los datos, hay varias cosas que puede hacer.
Primero, puede crear una función de división que convierta los datos almacenados en la lista en filas que se pueden unir. La función de división sería similar a esto:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
Cuando usa la función de división, puede dejar los datos en las múltiples filas o puede concatenar los valores nuevamente en una lista separada por comas:
;with cte as
(
select c.col1, t1.col2
from t1
inner join
(
select t2.col1, i.items col2
from t2
cross apply dbo.split(t2.col2, ',') i
) c
on t1.col1 = c.col2
)
select distinct c.col1,
STUFF(
(SELECT distinct ', ' + c1.col2
FROM cte c1
where c.col1 = c1.col1
FOR XML PATH ('')), 1, 1, '') col2
from cte c
Ver demostración.
Una última forma de obtener el resultado es aplicando FOR XML PATH
directamente.
select col1,
(
select ', '+t1.col2
from t1
where ','+t2.col2+',' like '%,'+cast(t1.col1 as varchar(10))+',%'
for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as col2
from t2;
Ver SQL Fiddle con demostración
Aquí hay una forma de dividir los datos sin una función y luego usar el estándar XML PATH
método para obtener la lista CSV:
with CTE as
(
select T2.col1
, T1.col2
from T2
inner join T1 on charindex(',' + T1.col1 + ',', ',' + T2.col2 + ',') > 0
)
select T2.col1
, col2 = stuff(
(
select ',' + CTE.col2
from CTE
where T2.col1 = CTE.col1
for xml path('')
)
, 1
, 1
, ''
)
from T2
Violín SQL con demostración.
Como se ha mencionado en otra parte de esta pregunta, es difícil consultar este tipo de datos desnormalizados de manera eficiente, por lo que su primera prioridad debe ser investigar la actualización de la estructura de la tabla, pero esto al menos permitirá obtener los resultados que necesita. .
Si desea hacer esta tarea en Oracle, podemos usar listagg
y puede lograrlo fácilmente.
Un posible equivalente disponible en SQL Server para listagg
es Stuff
Entonces, usando cosas que puede probar con la siguiente consulta:
SELECT T2.Col1,
Stuff((SELECT ',' + CAST(T1.Col2 AS VARCHAR(100))
FROM T1
WHERE T2.Col2 LIKE T1.Col1
FOR Xml Path('')),
1,
1,
'')
FROM T2