有没有比数据透视表或使用XML和节点更有效的滑动列的方法

Question

我想知道是否有其他方法可以拆分列或比这两种方法更好的方法

我将放置一些代码，以便我们可以使用相同的语言

--CREATING TABLE
CREATE TABLE BOOKS(
ID VARCHAR(MAX) NULL
) 
/*
BOOKS 
PRODUCTID, NAME, PAGES, WEIGHT, SIZE, TYPE
*/
INSERT INTO BOOKS (ID)
VALUES('B001,INTRODUCTION TO SQL,500,100G,MID,TECH')
,('B002,ADVANCED SQL SERVER PRACTICES,200,200G,BIG,TECH')
,('B003,SQL SERVER PERFORMANCE,1000,500G,BIG,TECH')
,('B004,SQL SERVER MANUAL,50,30G,SMALL,TECH')
,('B004,SQL SERVER MANUAL,50,30G,SMALL,TECH')

这将是我的演示表，如您所见，我有一个带有逗号分隔值的表

为了拆分此值，我将使用枢轴与 CTE 相结合

/*PIVOTING TABLE, ASIGNING A RN TO COLUMNS AND GETTING BACK VALUES*/
WITH C AS(
SELECT ID
,value 
,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT NULL)) as rn
FROM BOOKS BO
CROSS APPLY STRING_SPLIT(ID, ',') AS BK
)
SELECT ID
,[1] AS PRODUCTID
,[2] AS NAME
,[3] AS PAGES
,[4] AS WEIGHT
,[5] AS SIZE
,[6] AS TYPE
FROM C
PIVOT(
MAX(VALUE)
FOR RN IN([1],[2],[3],[4],[5],[6])  
) as PVT

它工作正常，但在这种情况下，它返回的字符串顺序无法保证(例如，1,2,3,4(。 列 1 = 1，列 2 = 3 列 3=2 列 4 =4，我期望得到 1,2,3,4 另一个是通过使用XML和节点的堆栈溢出中众所周知的

另一个是通过使用XML和节点的堆栈溢出中众所周知的

SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS PRODUCTID
,S.a.value('(/H/r)[2]', 'VARCHAR(100)')  AS NAME
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS PAGES
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS WEIGHT
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS SIZE
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS TYPE
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(ID, ',', '</r><r>')  
+ '</r></H>' AS XML) AS [vals]
FROM BOOKS) d 
CROSS APPLY d.[vals].nodes('/H/r') S(a) 

它也按预期工作，但不要误会我的意思，如果您不高于初学者水平，则很难解释并且有点混乱。

还有什么更好的方法可以在sql Server中拆分列，Microsoft是否为此实现了新功能，或者您知道另一种方法

。

Answer 1

就个人而言，我的方法是将值视为分隔项，然后使用交叉表对其进行透视。由于序号位置很重要，STRING_SPLIT并不能保证这一点，那么DelimitedSplit8k_LEAD在这里是一个更好的选择：

SELECT MAX(CASE DS.ItemNumber WHEN 1 THEN NULLIF(DS.Item,'') END) AS PRODUCTID,
MAX(CASE DS.ItemNumber WHEN 2 THEN NULLIF(DS.Item,'') END) AS [NAME],
MAX(CASE DS.ItemNumber WHEN 3 THEN NULLIF(DS.Item,'') END) AS PAGES,
MAX(CASE DS.ItemNumber WHEN 4 THEN NULLIF(DS.Item,'') END) AS WEIGHT,
MAX(CASE DS.ItemNumber WHEN 5 THEN NULLIF(DS.Item,'') END) AS SIZE,
MAX(CASE DS.ItemNumber WHEN 6 THEN NULLIF(DS.Item,'') END) AS [TYPE]
FROM dbo.BOOKS B
CROSS APPLY dbo.DelimitedSplit8K_LEAD(B.ID,',') DS
GROUP BY B.ID;

Answer 2

如果是2016+，另一个选择是JSON。

JSON 似乎优于 XML，尤其是在选择片段和选择值 ( ref (

示例 dbFiddle

Select B.* 
From BOOKS A
Cross Apply (
Select Pos1= JSON_VALUE(J,'$[0]')
,Pos2= JSON_VALUE(J,'$[1]')
,Pos3= JSON_VALUE(J,'$[2]')
,Pos4= JSON_VALUE(J,'$[3]')
,Pos5= JSON_VALUE(J,'$[4]')
,Pos6= JSON_VALUE(J,'$[5]')
From (values ('["'+replace(replace(ID,'"','"'),',','","')+'"]'))A(J)
) B