将分号分隔的列拆分为多列
我试图将SQL中的列拆分为多个列。将分号分隔的列拆分为多列
我的数据是这样的:
Column1 | Column2 | Column3
ABC | 123 | User7;User9
nbm | qre | User1;User2;User3
POI | kjh | User1;User4;User5;User9
我需要的栏3分成4分新列 - 包含第一个“用户”的每一列。此列中的每个值都用分号分隔。我遇到的问题之一是Column3可以列出任意数量的用户(全部用分号隔开),所以我不知道需要添加多少“新”列。
最终输出需要的样子:
Column1 | Column2 | Column3 | NewColumn1 | NewColumn2 | ETC.
除了这个事实,这是不好的设计,这里是一个解决方案:
就在这个粘贴到一个空的查询窗口,然后执行。适应您的需求...
declare @tbl TABLE(Column1 VARCHAR(15),Column2 VARCHAR(15),Column3 VARCHAR(150));
INSERT INTO @tbl VALUES
('ABC','123','User7;User9')
,('nbm','qre','User1;User2;User3')
,('POI','kjh','User1;User4;User5;User9');
WITH Splitted AS
(
SELECT Column1,Column2,CAST('<x>'+REPLACE(Column3,';','</x><x>')+'</x>' AS XML) AS Col3Splitted
FROM @tbl
)
SELECT Column1,Column2,Col3Splitted
,Col3Splitted.value('x[1]','varchar(max)') AS Column4
,Col3Splitted.value('x[2]','varchar(max)') AS Column5
,Col3Splitted.value('x[3]','varchar(max)') AS Column6
,Col3Splitted.value('x[4]','varchar(max)') AS Column7
/*Add as many as you need*/
FROM Splitted
在与@SeanLang的讨论后,我添加这种动态的方法。它将计算Column3中分号的最高数量,并动态构建上面的语句。
CREATE TABLE #tbl (Column1 VARCHAR(15),Column2 VARCHAR(15),Column3 VARCHAR(150));
INSERT INTO #tbl VALUES
('ABC','123','User7;User9')
,('nbm','qre','User1;User2;User3')
,('POI','kjh','User1;User4;User5;User9');
DECLARE @sql VARCHAR(MAX)=
'WITH Splitted AS
(
SELECT Column1,Column2,CAST(''<x>''+REPLACE(Column3,'';'',''</x><x>'')+''</x>'' AS XML) AS Col3Splitted
FROM #tbl
)
SELECT Column1,Column2';
DECLARE @counter INT = 0;
WHILE @counter<=(SELECT MAX(LEN(Column3) - LEN(REPLACE(Column3, ';', ''))) from #tbl)
BEGIN
SET @[email protected]+1;
SET @[email protected]+',Col3Splitted.value(''x[' + CAST(@counter AS VARCHAR(10)) + ']'',''varchar(max)'') AS Column' + CAST(@counter+3 AS VARCHAR(10));
END
SET @[email protected]+ ' FROM Splitted;';
EXEC (@sql);
DROP TABLE #tbl;
这可行,但不会支持未知数量的列要求。为此,它需要是动态的。 –
@SeanLange,是,否......您可以添加预期的最大列数,如果为空,它们将返回NULL。设计很糟糕。这会 - 至少 - 回来一个definit结构;-) – Shnugo
同意设计臭鸡蛋超过1000岁,但OP的确表明他们不知道有多少列将被退回。在这方面,你的方法不具有动态性。 –
这里是一个100%动态的方法。它将根据它找到的数据生成任意数量的列。这个围绕SO的主流方法是一个动态支点。我更喜欢动态交叉表,因为我觉得它的语法不那么呆板,而且从性能的角度来看它也有一点好处。 :)
这是一篇文章,很好地解释了这种方法。 http://www.sqlservercentral.com/articles/Crosstab/65048/
另外,我使用的是由Jeff Moden最初编写并且随着时间的推移通过社区改进的DelimitedSplit8K函数。你可以阅读它并在这里找到它的代码。 http://www.sqlservercentral.com/articles/Tally+Table/72993/
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something;
create table #something
(
Column1 varchar(5)
, Column2 varchar(5)
, Column3 varchar(50)
);
insert #something
select 'ABC', '123', 'User7;User9' union all
select 'nbm', 'qre', 'User1;User2;User3' union all
select 'POI', 'kjh', 'User1;User4;User5;User9';
declare @StaticPortion nvarchar(2000) = 'with orderedResults as
(
select s.Column1
, s.Column2
, x.Item
, x.ItemNumber
from #something s
cross apply dbo.DelimitedSplit8K(Column3, '';'') x
)
select Column1
, Column2
';
declare @DynamicPortion nvarchar(max) = '';
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select @DynamicPortion = @DynamicPortion + ', MAX(Case when ItemNumber = ' + CAST(N as varchar(6)) + ' then Item end) as Subject' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <=
(
select top 1 COUNT(*)
from #something s
cross apply dbo.DelimitedSplit8K(Column3, ';') x
group by Column1
Order by COUNT(*) desc
);
declare @FinalStaticPortion nvarchar(2000) = ' from orderedResults group by Column1, Column2 order by Column1, Column2';
declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;
select @SqlToExecute;
--uncomment the following line when you are sure this is what you want to execute.
--exec sp_executesql @SqlToExecute;
对于MySQl可以使用下面的代码。这只是一个示例代码
@num_Column3 := 1 + LENGTH(@Column3) - LENGTH(REPLACE(@Column3, '|', '')) AS num_Column3
,IF(@num_Column3 > 0, SUBSTRING_INDEX(SUBSTRING_INDEX(@Column3)), '|', 1), '|', -1), '') AS RC_user
是否有你需要在同一个表中的用户的原因?为什么不把它们分离到另一个表中,然后创建一个链接表来加入它们?这样你就不用太担心会有多少用户...... –
你实际上是想修改包含数据的表结构还是仅仅用于显示目的? – DeanOC
您遇到问题的原因是因为您的数据未正确归一化。您首先必须将该分隔列表拆分为可用的东西。然后,您将需要一个动态交叉表或数据透视表来生成您所需的动态列。 –