拆分sql server 2008中的列

Question

我有一张桌子

servername  description                                                                    ObjectState
VM1 SP LA -  W IN  S V R S #P19 99 9999 999999 999  QTY 1                     0
VM2 S PL A - V R STD #P29-9 9 99 QTY 2 : SPLVRENT #P3 999999 9 QTY 3    1
VM3 S PL A - WI NS V R S TD #P39-9 999 QTY 3    0
VM4 SPLA - WI NS V R STD #P59- 9 9 99 QTY 2 : S P LA - W IN SV RENT #P39-9999 QTY 3 :      SPL A - WIN S VR SMB # P 3 9- 999 99 QTY 5   1
VM6 SPLA - W I NS VRSQ LSE R VE RS TD #P 6 9-9 9 9 9 QTY 6  0
VM5 SPLA - W IN S VRS TD #P59-9999 QTY 5 : SPLA - SQLSERVER-08 #P59-9999 QTY 5  0
VM7 SPLA - W I NSV R ST D #P79-9999 QTY 6   0
VM8 SPLA - W INS VR STD #P8 9-9 9 99 QTY 1  1

我写了这样的查询

拆分description列，如下所示

SELECT
    T.servername,
    A.x.value('(/e/text())[1]', 'varchar(MAX)') [description],
    A.x.value('(/e/text())[2]', 'varchar(25)') [ponumber],
    A.x.value('(/e/text())[3]', 'varchar(2)') [qty],
    A.x.value('(/e/text())[4]', 'char(1)') [ObjectState]
FROM
    T1 AS T
    CROSS APPLY
    (SELECT T.[description] AS [text()] FOR XML PATH('')) AS R(s)
    CROSS APPLY
    (SELECT T.[ObjectState] as [text()] FOR XML PATH('')) AS B(t)
    CROSS APPLY
    (SELECT CAST('<e>' + REPLACE(R.s,':', '</e><e>') + '</e>' AS xml)) AS S(x)
    CROSS APPLY
    S.x.nodes('/e') AS N1(e)
    CROSS APPLY
    (SELECT CAST('<e>' + REPLACE(REPLACE(N1.e.value('text()[1]','varchar(MAX)'), '#', '</e><e>'), 'QTY', '</e><e>') + '</e>' AS xml)) AS A(x)

我得到了这样的输出

servername description           ponumber                  qty      ObjectStat             
VM1   SP LA -  W IN  S V R S    P19 99 9999 999999 999    1       NULL
VM2   S PL A - V R STD  P29-9 9 99          2                                                                NULL
VM2   SPLVRENT           P3 999999 9            3                                                                NULL

我想要列Objectstate中的输出

vm1 SP LA -  W IN  S V R S P19 99 9999 999999 999 1   0
vm2 S PL A - V R STD       P29-9 9 99             2   1
vm2  SPLVRENT              P3 999999 9            3   1

就像这样--我如何修改上面的代码来获得我需要的输出呢？

Answer 1

在您的查询中，看起来ObjectState是它自己的一个字段。所以..。

SELECT
    T.servername,
    A.x.value('(/e/text())[1]', 'varchar(MAX)') [description],
    A.x.value('(/e/text())[2]', 'varchar(25)') [ponumber],
    A.x.value('(/e/text())[3]', 'varchar(2)') [qty],
    T.ObjectState
FROM
    @T AS T
    CROSS APPLY
    (SELECT T.[description] AS [text()] FOR XML PATH('')) AS R(s)
    CROSS APPLY
    (SELECT CAST('<e>' + REPLACE(R.s,':', '</e><e>') + '</e>' AS xml)) AS S(x)
    CROSS APPLY
    S.x.nodes('/e') AS N1(e)
    CROSS APPLY
    (SELECT CAST('<e>' + REPLACE(REPLACE(N1.e.value('text()[1]','varchar(MAX)'), '#', '</e><e>'), 'QTY', '</e><e>') + '</e>' AS xml)) AS A(x)