SQL Server: come creare combinazioni di gerarchie da una tabella

Question

Ho i livelli di una gerarchia impilati in una tabella e voglio creare le combinazioni. Ho provato a utilizzare query ricorsive, ma non sono riuscito a capirlo. Sono sicuro che ci deve essere un modo semplice per farlo. Ho diverse gerarchie con un numero diverso di livelli, quindi non voglio scrivere un codice per ciascuno e voglio avere una query che gestisca il numero dei livelli. Gradirei qualsiasi aiuto!

Ecco il codice per creare i dati di esempio:

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

Questa è la query che ho generato il risultato desiderato per questa specifica gerarchia:

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

dati di esempio:

risultato desiderato:

Answer 1

Questa mi sembra solo un'aggregazione condizionale:

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;

In alternativa, puoi formulare questo come join:

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;

Ecco un violino db <>.

Answer 2

Possiamo anche farne uso, PIVOTma ancora una volta dobbiamo fornire un certo massimo levelnella clausola pivoting e per renderlo dinamico, devi pensare di convertirlo in Dynamic Sql(Sfortunatamente non ho molta familiarità con SQL Server Dynamic SQL)

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