將包含不同數量值的列拆分為多個列，SQLServer

我有下表名為Company，它有很多列，但這是有問題的：

每行都有不同數量的人，括號中是他們在公司中的職位。我們知道有7個可能的職位：清潔工、主管、經理、主管、秘書、HR、老板

所需的結果是該列將被多列替換 - 一種用于每種型別的職位，如果有人擔任該職位，則每行的他/她的名字出現在相應的列中。一旦名稱落在右列中，就不再需要括號中的位置，我只是將其留在結果表中以更好地說明結果。其余的單元格可以是空白的 N/A 或其他。

我知道如何在 python 中做到這一點，但我需要在 SQL Server 中實作它，不幸的是，這不是我的優勢之一。我查找了類似的問題，但我無法使其發揮作用。請幫忙。

uj5u.com熱心網友回復：

PIVOT關系運算子是一個選項：

樣本資料：

SELECT *
INTO Company
FROM (VALUES
   (1, 'Frodo B (manager), Gandalf G (director), Batman C (cleaner)'),
   (2, 'John Doe (secretary), Mark Jacobs (manager), Lilly Hopes (director), Rihanna Williams (cleaner), Maddy James (supervisor), Merry Poppins (HR)'),
   (3, 'Rick Ross (cleaner)'),
   (4, 'Orlando Bloom (manager), Keira Knightly (secretary)')
) d (Id, Team)

陳述：

SELECT *
FROM (
   SELECT c.Id, LTRIM(s.[value]) AS [Name], p.Position
   FROM Company c
   CROSS APPLY STRING_SPLIT(c.Team, ',') s
   JOIN (VALUES
      ('cleaner'), ('director'), ('manager'), ('supervisor'), ('secretary'), ('HR'), ('owner')
   ) p (Position) ON CHARINDEX(p.Position, s.[value]) > 0
) t
PIVOT (
   MAX(Name) 
   FOR Position IN ([cleaner], [director], [manager], [supervisor], [secretary], [HR], [owner])
) p

結果：

對于 2016 年之前的 SQL Server 版本（但支持 XML），您可以使用眾所周知的 XML 技術拆分存盤的文本：

SELECT *
FROM (
   SELECT c.Id, LTRIM(s.[value].value('.', 'varchar(1000)')) AS [Name], p.Position
   FROM (
      SELECT *, CAST('<x>'   REPLACE(Team, ',', '</x><x>')   '</x>' AS XML) AS XmlTeam
      FROM Company
   ) c
   CROSS APPLY c.XmlTeam.nodes('./x') s ([value])
   JOIN (VALUES
      ('cleaner'), ('director'), ('manager'), ('supervisor'), ('secretary'), ('HR'), ('owner')
   ) p (Position) ON CHARINDEX(p.Position, s.[value].value('.', 'varchar(1000)')) > 0
) t
PIVOT (
   MAX(Name) 
   FOR Position IN ([cleaner], [director], [manager], [supervisor], [secretary], [HR], [owner])
) p   

uj5u.com熱心網友回復：

遞回是另一個（知道它僅限于 7 次迭代）：

with MyTable as (
select *
from (values 
 (1, 'Frodo B (manager), Gandalf G (director), Batman C (cleaner)')
,(2, 'John Doe (secretary), Mark Jacobs(manager), Lilly Hopes(director), Rihanna Williams (cleaner), Maddy James (supervisor), Merry Poppins (HR)')
,(3, 'Rick Ross (cleaner)')
,(4,'Orlando Bloom (manager), Keira Knightly (secretary)')
) T (Id, Team)
),
RCTE as ( -- Find closing bracket positions
    select Id, FromPos=1 , BctPos=charindex(')',Team), Team from MyTable
    union all
    select R.Id, FromPos=R.BctPos 2, BctPos=charindex(')', M.Team, R.BctPos 1), M.Team 
    from MyTable M 
         inner join
         RCTE R
         on R.Id=M.Id
    where charindex(')',M.Team,R.BctPos 1)>0
),
Parsed as
( select Id, Team, Member=SubString(Team,FromPos,BctPos-FromPos 1)  from RCTE)
select 
  Id
, director=min(case when Member like '%(director)' then left(Member,len(Member)- charindex('(',Member)-1) end)
, cleaner=min(case when Member like '%(cleaner)' then left(Member,len(Member)- charindex('(',Member)-1) end)
, manager=min(case when Member like '%(manager)' then left(Member,len(Member)- charindex('(',Member)-1) end)
, supervisor=min(case when Member like '%(supervisor)' then left(Member,len(Member)- charindex('(',Member)-1) end)
, secretary=min(case when Member like '%(secretary)' then left(Member,len(Member)- charindex('(',Member)-1) end)
, owner=min(case when Member like '%(owner)' then left(Member,len(Member)- charindex('(',Member)-1) end)
from Parsed 
group by id
order by id

我假設它們都格式正確（括號正確打開關閉；并且始終使用空白，等等。

標籤：sql sql服务器 数据库 tsql 分裂

上一篇：來自django視圖的html表中的資料視圖

下一篇：從表中的許多列中過濾值