SQL 加入逗号分隔的数据列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16507239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
join comma delimited data column
提问by Mahdi Yousef
my table1 is :
我的表 1 是：
T1
T1
col1 col2
C1 john
C2 alex
C3 piers
C4 sara
and so table 2:
所以表2：
T2
T2
col1 col2
R1 C1,C2,C4
R2 C3,C4
R3 C1,C4
how to result this?:
如何导致这个？：
query result
查询结果
col1 col2
R1 john,alex,sara
R2 piers,sara
R3 john,sara
please help me?
请帮我？
回答by Taryn
Ideally, your best solution would be to normalize Table2 so you are not storing a comma separated list.
理想情况下，您最好的解决方案是规范化 Table2，这样您就不会存储逗号分隔的列表。
Once you have this data normalized then you can easily query the data. The new table structure could be similar to this:
将这些数据标准化后，您就可以轻松查询数据。新的表结构可能类似于：
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')
;
Normalizing the tables would make it much easier for you to query the data by joining the tables:
规范化表将使您更容易通过联接表来查询数据：
select t2.col1, t1.col2
from t2
inner join t1
on t2.col2 = t1.col1
See Demo
看演示
Then if you wanted to display the data as a comma-separated list, you could use FOR XML PATHand STUFF:
然后，如果您想将数据显示为逗号分隔的列表，您可以使用FOR XML PATHand 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;
See Demo.
见演示。
If you are not able to normalize the data, then there are several things that you can do.
如果您无法规范化数据，那么您可以做几件事。
First, you could create a split function that will convert the data stored in the list into rows that can be joined on. The split function would be similar to this:
首先，您可以创建一个拆分函数，它将存储在列表中的数据转换为可以连接的行。split 函数类似于：
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;
When you use the split, function you can either leave the data in the multiple rows or you can concatenate the values back into a comma separated list:
使用 split, 函数时，您可以将数据保留在多行中，也可以将值连接回逗号分隔列表：
;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
See Demo.
见演示。
A final way that you could get the result is by applying FOR XML PATHdirectly.
获得结果的最后一种方法是FOR XML PATH直接申请。
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;
回答by Ian Preston
Here's a way of splitting the data without a function, then using the standard XML PATHmethod for getting the CSV list:
这是一种无需函数即可拆分数据的方法，然后使用标准XML PATH方法获取 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
As has been mentioned elsewhere in this question it is hard to query this sort of denormalised data in any sort of efficient manner, so your first priority should be to investigate updating the table structure, but this will at least allow to get the results you require.
正如本问题其他地方所提到的，很难以任何有效的方式查询此类非规范化数据，因此您的首要任务应该是调查更新表结构，但这至少可以得到您需要的结果.
回答by pratik garg
If you wanted to do this task in oracle we can use listaggand can accomplish this easily.
如果您想在 oracle 中完成此任务，我们可以使用listagg并且可以轻松完成此任务。
A possible equivalent available in SQL Server for listaggis Stuff
SQL Server 中可用的可能等效项listagg是Stuff
So using stuff you can try with following query:
因此，使用您可以尝试使用以下查询的东西：
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
回答by Behrouz Bakhtiari
First write a table value function for split col2 on tbl2.
首先为tbl2上的split col2写一个表值函数。
CREATE FUNCTION [dbo].[Split](@String varchar(100), @Delimiter char(1))
returns @temptable TABLE (items VARCHAR(5))
as
begin
declare @idx int
declare @slice VARCHAR(5)
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
Go
;WITH SplitList
AS ( SELECT T2.Col1 ,
T1.Col2
FROM T2
CROSS APPLY dbo.Split(T2.Col2, ',') S
INNER JOIN T1 ON T1.Col1 = S.Items
)
SELECT T2.Col1 ,
STUFF(( SELECT ', ' + SplitList.Col2
FROM SplitList
WHERE SplitList.Col1 = T2.Col1
FOR
XML PATH('')
), 1, 2, '')
FROM T2
回答by bitmagier
This task cannot be solved with standard SQL. In Oracle I would write a stored function (PL/SQL) to parse the Name-ID-string (T2 col2) and resolve the names. Don't know if that's possible in Transact-SQL, but it's glorious inefficient.
使用标准 SQL 无法解决此任务。在 Oracle 中，我会编写一个存储函数 (PL/SQL) 来解析 Name-ID-string (T2 col2) 并解析名称。不知道这在 Transact-SQL 中是否可行，但它非常低效。
T2 is a badly designed, not normalized table. That's the problem. If you would normalize it, so that you have one line per Name-ID (col 2 in T2), you can get the list of names with a simple join of the two tables. To generate the desired output format (comma delimited) you need to write something else than SQL - maybe a stored procedure or something else that iterates over the resultset.
T2 是一个设计糟糕的表，不是规范化的表。那就是问题所在。如果您将其标准化，以便每个 Name-ID（T2 中的第 2 列）有一行，则可以通过简单连接两个表来获得名称列表。要生成所需的输出格式（逗号分隔），您需要编写 SQL 以外的其他内容 - 可能是存储过程或其他迭代结果集的内容。
回答by Glen
If you are like me and you are a stickler for CTE's especially recursive CTE's as supposed to STUFF and XML Path:
如果你像我一样，并且坚持 CTE 的特别递归 CTE，因为它应该是 STUFF 和 XML 路径：
DECLARE @T1 TABLE (
col1 CHAR(2),
col2 VARCHAR(10)
)
INSERT INTO @T1
VALUES ('C1', 'john'),
('C2', 'alex'),
('C3', 'piers'),
('C4', 'sara');
DECLARE @T2 TABLE (
col1 CHAR(2),
col2 CHAR(100)
)
INSERT INTO @T2
VALUES ('R1', 'C1,C2,C4'),
('R2', 'C3,C4'),
('R3', 'C1,C4');
WITH T2Sorted AS (
SELECT col1, col2, RN = ROW_NUMBER() OVER (ORDER BY col1) FROM @T2
), CTERecursionOnT2 AS (
SELECT RN, col1, col2, 0 AS PrevCharIndex, CHARINDEX(',', col2, 1) AS NextCharIndex FROM T2Sorted
UNION ALL
SELECT a.RN, a.col1, a.col2, b.NextCharIndex, CHARINDEX(',', a.col2, b.NextCharIndex + 1)
FROM T2Sorted a
JOIN CTERecursionOnT2 b ON a.RN = b.RN
WHERE b.NextCharIndex > 0
), CTEIndividualCol2Items AS (
SELECT *, SUBSTRING(col2, PrevCharIndex + 1, CASE WHEN NextCharIndex = 0 THEN LEN(col2) ELSE NextCharIndex - 1 END - PrevCharIndex) AS itemCol2
FROM CTERecursionOnT2
), CTELookupT1 AS (
SELECT a.col1, b.col2, RN = ROW_NUMBER() OVER (PARTITION BY a.col1 ORDER BY a.PrevCharIndex)
FROM CTEIndividualCol2Items a
JOIN @T1 b ON a.itemCol2 = b.col1
), CTERecursionOnLookupT1 AS (
SELECT col1, CAST(col2 AS VARCHAR(MAX)) AS col2, RN
FROM CTELookupT1
WHERE RN = 1
UNION ALL
SELECT a.col1, b.col2 + ',' + a.col2, a.RN
FROM CTELookupT1 a
JOIN CTERecursionOnLookupT1 b ON a.col1 = b.col1 AND a.RN = b.RN + 1
), CTEFinal AS (
SELECT *, RNDesc = ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY RN DESC)
FROM CTERecursionOnLookupT1
)
SELECT col1, col2
FROM CTEFinal
WHERE RNDesc = 1
ORDER BY col1
Obviously you could break up the first recursion part into separate functions as the already agreed solution has suggested i.e. CTERecursionOnT2 and thus CTEIndividualCol2Items can be your alternative Split function (I would include the order id as well), and thus:
显然，您可以将第一个递归部分分解为单独的函数，因为已经商定的解决方案建议即 CTERecursionOnT2，因此 CTEIndividualCol2Items 可以是您的替代 Split 函数（我也会包括订单 ID），因此：
;WITH CTEIndividualCol2Items AS (
SELECT a.col1, b.value as itemCol2, b.id AS PrevCharIndex
FROM @T2 a
CROSS APPLY (
SELECT id, items FROM dbo.Split(a.col2, ',')
) b
) ...
and you split function:
你拆分功能：
CREATE FUNCTION dbo.Split(@String varchar(100), @Delimiter char(1))
RETURNS TABLE
AS
RETURN
(
WITH CTERecursion AS (
SELECT id = 1, PrevCharIndex = 0, NextCharIndex = CHARINDEX(@Delimiter, @String, 1)
UNION ALL
SELECT id + 1, NextCharIndex, CHARINDEX(@Delimiter, @String, NextCharIndex + 1) FROM CTERecursion WHERE NextCharIndex > 0
)
SELECT Id, items = SUBSTRING(@String, PrevCharindex + 1, (CASE WHEN NextCharIndex = 0 THEN LEN(@String) ELSE NextCharIndex - 1 END) - PrevCharIndex)
FROM CTERecursion
WHERE @String > ''
)
