将逗号分隔的值放入单独的或不同的列中

一尘不染

将逗号分隔的值放入单独的或不同的列中

sql

我有一个表和值，如下所示

create table #example(id int primary key identity, cols varchar(255))
insert into #example(cols) values('HI,HELLO,BYE,TC')
insert into #example(cols) values('WHAT,ARE,YOU,DOING,HERE')

我需要如图所示的结果输出

注意： 值没有限制，即动态

阅读 133

2021-05-23

共1个答案

一尘不染

这就是你所期望的

架构：

CREATE TABLE #EXAMPLE(ID INT PRIMARY KEY IDENTITY, COLS VARCHAR(255))
INSERT INTO #EXAMPLE(COLS) VALUES('HI,HELLO,BYE,TC')
INSERT INTO #EXAMPLE(COLS) VALUES('WHAT,ARE,YOU,DOING,HERE')

请将那些用逗号分隔的值拆分为“行”并应用“透视”

SELECT * FROM (
SELECT id
, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS ID2
, SPLT.CLMS.value('.','VARCHAR(MAX)') AS LIST FROM ( 
select id
, CAST( '<M>'+REPLACE(cols,',','</M><M>')+'</M>' AS XML) AS XML_COL from #example E
)E
CROSS APPLY  E.XML_COL.nodes('/M') AS SPLT(CLMS)
)A
PIVOT
(
    MAX(LIST) FOR ID2 IN ([1],[2],[3],[4],[5])
)PV

你会得到的结果

+----+------+-------+-----+-------+------+
| id |  1   |   2   |  3  |   4   |  5   |
+----+------+-------+-----+-------+------+
|  1 | HI   | HELLO | BYE | TC    | NULL |
|  2 | WHAT | ARE   | YOU | DOING | HERE |
+----+------+-------+-----+-------+------+

编辑：

现在，您需要进行动态数据透视，因为值没有限制。

DECLARE @COLS VARCHAR(MAX)='', @QRY VARCHAR(MAX)='';


SELECT @COLS =@COLS+'['+CAST( ID2 AS VARCHAR(10))+'],' FROM (
SELECT  DISTINCT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS ID2  FROM ( 
select id, CAST( '<M>'+REPLACE(cols,',','</M><M>')+'</M>' AS XML) AS XML_COL from #example E
)E
CROSS APPLY  E.XML_COL.nodes('/M') AS SPLT(CLMS)
)A

SELECT @COLS = LEFT(@COLS,LEN(@COLS)-1)


SELECT @QRY =
'
SELECT * FROM (
SELECT id
, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS ID2
, SPLT.CLMS.value(''.'',''VARCHAR(MAX)'') AS LIST FROM ( 
select id, CAST( ''<M>''+REPLACE(cols,'','',''</M><M>'')+''</M>'' AS XML) AS XML_COL from #example E
)E
CROSS APPLY  E.XML_COL.nodes(''/M'') AS SPLT(CLMS)
)A
PIVOT
(
    MAX(LIST) FOR ID2 IN ('+@COLS+ ')
)PV'

EXEC( @QRY)

2021-05-23