Mam kolumnę oddzieloną kartę w DB, który zawiera pole nagłówka i powiązane wyniki. Potrzebuję pomocy w zapytaniu, który zwróci te dane w oddzielnych wierszach na podstawie kolumny IP. Poniżej znajduje się próbka danych, a także pożądane wyniki.

Poniższe zapytanie daje ten wynik.

DECLARE @Tmp TABLE (ID int Identity(1,1), IP nvarchar(255),Results NVARCHAR(max));
INSERT into @Tmp select top 1 ip, Results from MyTable where results like 'Package%'
select * from @Tmp

enter image description here

Jest to pożądany wynik.

IP              Package      Installed Version          Required Version
10.48.8.28      nss          3.21.0-9.el7_2.x86_64      3.21.3-2.el7_3
10.48.8.28      nss-sysinit  3.21.0-9.el7_2.x86_64      3.21.3-2.el7_3
10.48.8.28      nss-tools    3.21.0-9.el7_2.x86_64      3.21.3-2.el7_3
10.48.8.28      nss-util     3.21.0-2.2.el7_2.x86_64    3.21.3-1.1.el7_3

Jest to dane, które skopiowane z okna wyników SQL do schowka.

ID  IP          Results
1   10.46.8.28  Package Installed Version   Required Version
                python  2.7.5-39.el7_2.x86_64   2.7.5-48.el7
                python-libs 2.7.5-39.el7_2.x86_64   2.7.5-48.el7
3
Eddie D 15 luty 2017, 23:19

2 odpowiedzi

Najlepsza odpowiedź

Utwórz przykładowe dane

Declare @YourTable table (ID int,IP varchar(50),TabString varchar(max))
Insert Into @YourTable values
(1,'444.333.222.11','Package Installed Version  Required Version
nss 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-sysinit 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-tools   3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-util    3.21.0-2.2.el7_2.x86_64 3.21.3-1.1.el7_3')

Co wygląda tak

enter image description here

Opcja 1 (z funkcjami analizy)

Select A.ID
      ,A.IP
      ,Package             = Pos1
      ,[Installed Version] = Pos2
      ,[Required Version]  = Pos3
 From  @YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.TabString,char(13)) B
 Cross Apply [dbo].[udf-Str-Parse-Row](B.RetVal,char(9)) C
 Where B.RetVal is not null and B.RetSeq>1

Zwroty

enter image description here

Opcja 2 (bez funkcji analiza)

Select A.ID
      ,A.IP
      ,Package             = Pos1
      ,[Installed Version] = Pos2
      ,[Required Version]  = Pos3
 From  @YourTable A
 Cross Apply ( 
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.TabString,char(13),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
                Cross Apply x.nodes('x') AS B(i)
             ) B
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(B.RetVal,char(9),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as X 
             ) C
 Where B.RetVal is not null and B.RetSeq>1

Zwroty

enter image description here

Funkcje analizy, jeśli jesteś zainteresowany

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  (Select Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
)
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John <test> Cappelletti',' ')
--Select * from [dbo].[udf-Str-Parse-Row]('A&B;C;D;E, F;<x>',';')
0
John Cappelletti 15 luty 2017, 22:53

Jeśli używasz SQL Server 2016, możesz użyć STRING_SPLIT()

Według Aarona Bertrand to najszybszy sposób. Zobacz to https://sqlperformance.com/2016/03/sql- Serwer-2016 / Split String

Declare @YourTable table (ID int,IP varchar(50),TabString varchar(max));
Insert Into @YourTable values
(1,'444.333.222.11','Package    Installed Version   Required Version
nss 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-sysinit 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-tools   3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-util    3.21.0-2.2.el7_2.x86_64 3.21.3-1.1.el7_3');


;WITH CTE_Lines AS
(
select ID, IP, a.ROWID , a.Lines from @YourTable
    CROSS APPLY (
        SELECT 
           ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ROWID, value Lines
         FROM STRING_SPLIT(TabString, CHAR(13)) --Splits by new lines
    ) a
), CTE_Columns AS
(
SELECT ID, IP, ROWID, b.ColsID, b.[Cols] FROM CTE_Lines
    CROSS APPLY (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ColsID,
        value [Cols] 
        FROM STRING_SPLIT(Lines, CHAR(9)) --Splits by tabs
    ) b 
    WHERE ROWID <> 1 --Removes the headers
)

SELECT ID, IP,  [1] Package, [2] [Installed Version],[3] [Required Version]  FROM 
(
    SELECT ID, IP, ROWID, ColsID, [Cols]  FROM CTE_Columns 
) a
PIVOT (MAX(a.Cols) FOR ColsId in ([1],[2],[3])) as pvt
1
Lobo 17 luty 2017, 11:40