Sql-Server
如何展平和旋轉規範化層次結構表
我有一個看起來像這樣的 OrgChart 表：
**PositionCode PositionName ParentCode Level** AA0001 CEO 0 AA0002 首席財務官 AA0001 1 AA0003 首席資訊官 AA0002 2 AA0004 SnrMgr AA0002 2 AA0005 經理經理 AA0004 3 AA0006 ItMgr AA0003 3等等
因此，ItMgr 向 CIO 匯報，CIO 向 CFO 匯報，CFO 向 CEO 匯報。
我需要將其轉換為這樣的扁平結構（對於 BI 解決方案）：（
CurCode 是我們針對該行關注的位置，然後是該位置上方的完整組織層次結構）。**CurCode Lvl0Code Lvl0Name Lvl1Code Lvl1Name Lvl2Code Lvl2Name Lvl3Code Lvl3Name** AA0001 AA0001 CEO na na na na na na AA0002 AA0001 首席執行官 AA0002 首席財務官 na na na na na AA0003 AA0001 首席執行官 AA0002 首席財務官 AA0003 首席資訊官 na na AA0004 AA0001 首席執行官 AA0002 首席財務官 AA0004 SnrMgr na na AA0005 AA0001 首席執行官 AA0002 首席財務官 AA0004 經理經理 AA0005 經理經理 AA0006 AA0001 CEO AA0002 CFO AA0003 CIO AA0006 ItMgr如何在 T-SQL 中實現這一點？
該表有 5 層深，所以我不需要未定義的列數。
我正在查看 PIVOT，但看不到如何使其正常工作。
任何幫助將不勝感激。
只要您使用分層結構，您就可以應用遞歸解決方案。但是對於大量行，您必須處理 MAXRECURSION，請查看有關它的MS Docs 。
引用自文件：
錯誤組合的遞歸 CTE 可能會導致無限循環。例如，如果遞歸成員查詢定義為父列和子列返回相同的值，則會創建一個無限循環。為了防止無限循環，您可以通過在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之間的值來限制特定語句允許的遞歸級別數。這使您可以控制語句的執行，直到您解決創建循環的程式碼問題。伺服器範圍的預設值為 100。指定 0 時，不應用任何限制。每個語句只能指定一個 MAXRECURSION 值。
與樹作為 ( 選擇 PositionCode 作為 CurrCode， 位置程式碼， PositionCode 為 Lvl0Code， PositionName 為 Lvl0Name， cast('na' as nvarchar(10)) 作為 Lvl1Code， cast('na' as nvarchar(10)) 作為 Lvl1Name， cast('na' as nvarchar(10)) 作為 Lvl2Code， cast('na' as nvarchar(10)) as Lvl2Name, cast('na' as nvarchar(10)) 作為 Lvl3Code， cast('na' as nvarchar(10)) as Lvl3Name, 家長程式碼， 等級 從 表 在哪裡 等級 = 0 聯合所有 選擇 樹.CurrCode, tbl.PositionCode, tree.Lvl0程式碼, 樹.Lvl0Name, 如果 tbl.Level = 1 然後 tbl.PositionCode else tree.Lvl1Code 以 Lvl1Code 結尾， 如果 tbl.Level = 1 然後 tbl.PositionName else tree.Lvl1Name 以 Lvl1Name 結尾， 如果 tbl.Level = 2 然後 tbl.PositionCode else tree.Lvl2Code 以 Lvl2Code 結尾， 如果 tbl.Level = 2 然後 tbl.PositionName else tree.Lvl2Name 以 Lvl2Name 結尾， 如果 tbl.Level = 3 然後 tbl.PositionCode else tree.Lvl3Code 以 Lvl3Code 結尾， 如果 tbl.Level = 3 然後 tbl.PositionName else tree.Lvl3Name 以 Lvl3Name 結尾， tbl.ParentCode, 表層級 從 表 加入 樹 ON tree.PositionCode = tbl.ParentCode ) 選擇 課程程式碼， Lvl0程式碼， Lvl0名稱， Lvl1程式碼， Lvl1名稱， Lvl2程式碼， Lvl2名稱， Lvl3程式碼， Lvl3名稱 從 樹 選項（最大遞歸 0）；目前程式碼 | Lvl0程式碼 | Lvl0名稱 | Lvl1程式碼 | Lvl1姓名 | Lvl2程式碼 | Lvl2名稱 | Lvl3程式碼 | Lvl3名稱 :------- | :------- | :------- | :------- | :------- | :------- | :------- | :------- | :------- AA0001 | AA0001 | CEO 上 | 上 | 上 | 上 | 上 | 和 AA0001 | AA0001 | CEO AA0002 | 首席財務官 | 上 | 上 | 上 | 和 AA0001 | AA0001 | CEO AA0002 | 首席財務官 | AA0003 | 首席資訊官 | 上 | 和 AA0001 | AA0001 | CEO AA0002 | 首席財務官 | AA0004 | 管理員 | 上 | 和 AA0001 | AA0001 | 首席執行官 | AA0002 | 首席財務官 | AA0004 | 管理員 | AA0005 | 經理人 AA0001 | AA0001 | 首席執行官 | AA0002 | 首席財務官 | AA0003 | 首席資訊官 | AA0006 | 管理器db<>在這裡擺弄
如果您不喜歡或不想要或根本無法使用遞歸解決方案，您可以使用一系列嵌套的 CTE（每個級別一個），最後使用 UNION 操作組合結果：
與 l0 作為 ( 選擇 PositionCode 作為 CurrCode，PositionCode 作為 Lvl0Code，PositionName 作為 Lvl0Name， 'na' 作為 Lvl1Code， 'na' 作為 Lvl1Name， 'na' 作為 Lvl2Code， 'na' 作為 Lvl2Name， 'na' 作為 Lvl3Code， 'na' 作為 Lvl3Name， 等級 來自 tbl WHERE 級別 = 0 ), l1 作為 ( 選擇 l0.CurrCode、l0.Lvl0Code、l0.Lvl0Name、 t1.PositionCode Lvl1Code, t1.PositionName Lvl1Name, 'na' 作為 Lvl2Code， 'na' 作為 Lvl2Name， 'na' 作為 Lvl3Code， 'na' 作為 Lvl3Name， t1.level 從 tbl t1 加入 l0 ON t1.ParentCode = l0.Lvl0Code 哪裡 t1.level = 1 ), l2 AS ( 選擇 l1.CurrCode、l1.Lvl0Code、l1.Lvl0Name、 l1.Lvl1程式碼， l1.Lvl1名稱， t2.PositionCode Lvl2Code, t2.PositionName Lvl2Name, 'na' 作為 Lvl3Code， 'na' 作為 Lvl3Name， t2.level 從 tbl t2 加入 l1 ON t2.ParentCode = l1.Lvl1Code 其中 t2.level = 2 ), l3 作為 ( 選擇 l2.CurrCode、l2.Lvl0Code、l2.Lvl0Name、 l2.Lvl1程式碼， l2.Lvl1名稱， l2.Lvl2程式碼， l2.Lvl2Name, t3.PositionCode Lvl3Code, t3.PositionName Lvl3Name, t3.level 從 tbl t3 加入 l2 ON t3.ParentCode = l2.Lvl2Code 哪裡 t3.level = 3 ) 從 l0 中選擇 * 聯盟 選擇 * 從 l1 聯盟 從 l2 中選擇 * 聯盟 從 l3 中選擇 * 按 CurrCode 排序，級別；db<>在這裡擺弄
我的輸出對於您給定的輸入是正確的，但您應該嘗試使用其他範例數據。
解釋一下。告訴我它在哪些樣本數據中不起作用。
在現實生活中，一次將操作多少行？
declare @t table(PositionCode varchar(20),PositionName varchar(20) ,ParentCode varchar(20),Levels int) insert into @t values ('AA0001','CEO', null ,0) ,('AA0002','CFO','AA0001',1) ,('AA0003','CIO','AA0002',2) ,('AA0004','SnrMgr','AA0002',2) ,('AA0005','JnrMgr','AA0004',3) ,('AA0006','ItMgr','AA0003',3) ;With CTE as ( select PositionCode as CurCode,PositionName ,levels ,(select top 1 case when t.levels<0 then 'na' else t1.PositionCode end from @t t1 where levels=case when t.levels>=0 then 0 else t.levels end)[Lvl0Code] ,(select top 1 case when t.levels<1 then 'na' else t1.PositionCode end from @t t1 where levels=case when t.levels>=1 then 1 else t.levels end)[Lvl1Code] ,(select top 1 case when t.levels<2 then 'na' else case when t1.Levels=t.Levels then t.PositionCode else t.ParentCode END end from @t t1 where levels=case when t.levels>=2 then 2 else t.levels end)[Lvl2Code] ,(select top 1 case when t.levels<3 then 'na' else case when t1.Levels=t.Levels then t.PositionCode else t.ParentCode END end from @t t1 where levels=case when t.levels>=3 then 3 else t.levels end)[Lvl3Code] from @t t ) select CurCode,[Lvl0Code] ,isnull((select PositionName from @t t where positioncode=[Lvl0Code]),'na')[Lvl0Name] ,[Lvl1Code] ,isnull((select PositionName from @t t where positioncode=[Lvl1Code]),'na')[Lvl1Name] ,[Lvl2Code] ,isnull((select PositionName from @t t where positioncode=[Lvl2Code]),'na')[Lvl2Name] ,[Lvl3Code] ,isnull((select PositionName from @t t where positioncode=[Lvl3Code]),'na')[Lvl3Name] from cte c