SQL Server: So erstellen Sie Hierarchiekombinationen aus einer Tabelle

Question

Ich habe die Ebenen einer Hierarchie in einer Tabelle gestapelt und möchte die Kombinationen erstellen. Ich habe versucht, rekursive Abfragen zu verwenden, konnte es aber nicht herausfinden. Ich bin sicher, dass es einen einfachen Weg geben muss, dies zu tun. Ich habe verschiedene Hierarchien mit unterschiedlicher Anzahl von Ebenen, daher möchte ich nicht für jede Ebene einen Code schreiben und eine Abfrage haben, die die Anzahl der Ebenen behandelt. Ich würde mich über jede Hilfe freuen!

Hier ist der Code zum Erstellen der Beispieldaten:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [tmp].[tblSample](
    [hier] [nvarchar](255) NULL,
    [lvl] [nvarchar](255) NULL,
    [id] [int] NULL
) ON [PRIMARY]
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA00010102', N'3', 3)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA00019999', N'3', 4)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA00020107', N'3', 6)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA00029999', N'3', 7)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA11810001', N'3', 9)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA11812087', N'3', 10)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA11852299', N'3', 12)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA1185', N'2', 12)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 12)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA1181', N'2', 10)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 10)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA1181', N'2', 9)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 9)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA0002', N'2', 7)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 7)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA0002', N'2', 6)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 6)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA0001', N'2', 4)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 4)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA0001', N'2', 3)
GO
INSERT [tmp].[tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 3)
GO

Dies ist die Abfrage, mit der ich mein gewünschtes Ergebnis für diese bestimmte Hierarchie generiert habe:

SELECT t1.hier, t2.hier, t3.hier FROM tblSample t1 
    INNER JOIN tblSample t2 ON t1.id=t2.id AND t2.lvl=t1.lvl+1 
    INNER JOIN tblSample t3 ON t1.id=t3.id  AND t3.lvl=t1.lvl+2

Beispieldaten:

erwünschtes Ergebnis:

Answer 1

Das sieht für mich nur nach einer bedingten Aggregation aus:

select max(case when lvl = 1 then hier end),
       max(case when lvl = 2 then hier end),
       max(case when lvl = 3 then hier end)
from tblSample
group by id;

Alternativ können Sie dies als Verknüpfungen formulieren:

select s.hier, s2.hier, s3.hier
from tblSample s join
     tblSample s2
     on s2.lvl = s.lvl + 1 and
        s2.id = s.id join
     tblSample s3
     on s3.lvl = s2.lvl + 1 and
        s3.id = s2.id
where s.lvl = 1;

Hier ist eine db <> Geige.

Answer 2

Wir können auch davon Gebrauch machen, PIVOTaber wir müssen wieder ein bestimmtes Maximum levelin der Pivoting-Klausel angeben und um es dynamisch zu machen, müssen Sie darüber nachdenken, es zu konvertieren Dynamic Sql(leider bin ich mit dynamischem SQL von SQL Server nicht sehr vertraut).

select id
      ,[1] as hier1
      ,[2] as hier2
      ,[3] as hier3
from 
(
  select t1.hier,t1.lvl,t1.id
  from tblsample t1
) src
pivot
(
  max(hier)
  for lvl in ([1], [2], [3])
) piv;

Demo