How to extract a hierarchical structure from a flat table in SQL Server database

aemubtdh  于 2023-05-16  发布在  SQL Server
关注(0)|答案(3)|浏览(219)

I want to extract a hierarchical structure from a table in the sql server database. Table look similar to this with levels going till lvl 10:
| lvl1 | lvl2 | lvl3 |
| ------------ | ------------ | ------------ |

I want to extract a hierarchical structure from a table in the sql server database. Table look similar to this with levels going till lvl 10:

lvl1lvl2lvl3
OracleMarketing unitInternet
OracleLawyers unitIntellectual
OracleFinance unitnull
OracleLawyers unitJudicial department
OracleIT unitDatabase
OracleMarketing unitTelevision
OracleIT unitERP

That's what I want to get:

  • Oracle

  • Marketing unit

  • Internet

  • Television

  • Lawyers unit

  • Intellectual

  • Judicial department

  • Finance unit

  • IT unit

  • Database

  • ERP

I read about SQL hierarchical queries, but I have no idea how to do that with my table structure...

I would be grateful for the help, any ideas?

I have used unpivot, the result

Oracle
-list of all level 2
-list of all level 3
OracleMarketing unitInternet
OracleLawyers unitIntellectual
OracleFinance unitnull
OracleLawyers unitJudicial department
OracleIT unitDatabase
OracleMarketing unitTelevision
OracleIT unitERP
s3fp2yjn

s3fp2yjn1#

I'm not sure what you intend to use these results for, most cases would want some sort of relationship defined, but to replicated your stated required output you could use:

DECLARE @TABLE TABLE (lvl1 NVARCHAR(50), lvl2 NVARCHAR(50), lvl3 NVARCHAR(50));
INSERT INTO @TABLE (lvl1, lvl2, lvl3) VALUES
('Oracle', 'Marketing unit', 'Internet           '), ('Oracle', 'Lawyers unit  ', 'Intellectual       '), 
('Oracle', 'Finance unit  ', NULL),                  ('Oracle', 'Lawyers unit  ', 'Judicial department'), 
('Oracle', 'IT unit       ', 'Database           '), ('Oracle', 'Marketing unit', 'Television         '), 
('Oracle', 'IT unit       ', 'ERP                '); 

;WITH base AS (
SELECT DISTINCT lvl1, '' AS lvl2, '' AS lvl3
  FROM @TABLE
UNION ALL
SELECT DISTINCT lvl1, lvl2, ''
  FROM @TABLE
UNION ALL
SELECT DISTINCT lvl1, lvl2, lvl3
  FROM @TABLE
 WHERE lvl3 IS NOT NULL
)

SELECT CONCAT(CASE WHEN LAG(lvl1,1) OVER (ORDER BY lvl1, lvl2, lvl3) IS NULL THEN lvl1 WHEN LAG(lvl1,1) OVER (ORDER BY lvl1, lvl2, lvl3) = lvl1 THEN CHAR(9)+' - ' ELSE lvl1 END,
       CASE WHEN LAG(lvl2,1) OVER (ORDER BY lvl1, lvl2, lvl3) IS NULL THEN lvl2 WHEN LAG(lvl2,1) OVER (ORDER BY lvl1, lvl2, lvl3) = lvl2 THEN CHAR(9)+' - ' ELSE lvl2 END,
       lvl3)
  FROM base
 ORDER BY lvl1, lvl2, lvl3
lvl1lvl2lvl3
Oracle
- Finance unit
- IT unit
-- Database
-- ERP
- Lawyers unit
-- Intellectual
-- Judicial department
- Marketing unit
-- Internet
-- Television

All we're doing here is making seperate rows for the groups, and then sticking them back together, while comparing this row to the last row using LAG() .

ltskdhd1

ltskdhd12#

Your structure is a little clumsy, but this would be easy to expand up to 10 levels

You may notice the siblings are in alphabetical order, if you wanted a specific order, there would need to be another column with the proper order/sequence.

Example or dbFiddle

DECLARE @YourTable TABLE (lvl1 VARCHAR(50), lvl2 VARCHAR(50), lvl3 VARCHAR(50));
INSERT INTO @YourTable (lvl1, lvl2, lvl3) VALUES
('Oracle', 'Marketing unit', 'Internet'), ('Oracle', 'Lawyers unit  ', 'Intellectual       '), 
('Oracle', 'Finance unit', NULL),                  ('Oracle', 'Lawyers unit  ', 'Judicial department'), 
('Oracle', 'IT unit', 'Database'), ('Oracle', 'Marketing unit', 'Television         '), 
('Oracle', 'IT unit', 'ERP'); 

;with cte0 as ( Select Distinct Pt=Lvl1+null,ID=Lvl1 From @YourTable Where Lvl1 is not null )
     ,cte1 as ( Select Distinct Pt=Lvl1     ,ID=Lvl2 From @YourTable Where Lvl2 is not null )
     ,cte2 as ( Select Distinct Pt=Lvl2     ,ID=Lvl3 From @YourTable Where Lvl3 is not null )
     ,cteC as (
                Select * from cte0
                Union All
                Select * from cte1
                Union All
                Select * from cte2
              ),
     cteOH as (
     Select *,Lvl=1,SPath=convert(varchar(500),ID) From cteC Where Pt is null
     Union All
     Select h.Pt,h.ID,Lvl=cteOH.Lvl+1,sPath=convert(varchar(500),cteOH.sPath+'/'+h.ID) FROM cteC H join cteOH ON h.Pt = cteOH.ID
    )
    Select Lvl
          ,ID
          ,Pt
          ,Nested = replicate('|---',Lvl-1)+ID
     From cteOH
     Order By sPath

Results

dgtucam1

dgtucam13#

I think there may be a simpler way, unless I am missing the point. Why not simply group and sort? You can do a series of these with "go" in the middle, or just one big table:

SELECT lvl1, lvl2, lvl3, ... lvn
FROM Table
ORDER BY lvl2, lvl3,....lvn;

Or do a summary queries of each level

select lvl2,lvl3
from Table
order by lvl3
go
select lvl3,lvl4
from Table
order by lvl4
go
...
select lv(n-1),n
from Table
order by n

But I might be missing the point, because this is simple. I just wanted to include it because someone gave me a simple answer once, and it was the correct one.

相关问题