Convert columns with same name to proper rows

Question

I have the following table format:

CREATE TABLE tmp_tbl 
(
    gcode_id    int,
    g1code      varchar(20),
    g2code      varchar(20),
    g3code      varchar(20),
    g4code      varchar(20)
);

And the following values are inserted:

insert into tmp_tbl values (1, 'AFR', 'AFR-EAST', 'BI', 'BIBJM');
insert into tmp_tbl values (2, 'AFR', 'AFR-SOUTH', 'ZA', 'ZACPT');
insert into tmp_tbl values (3, 'EUR', 'EUR-BRI_IS', 'GB', 'GBMAN');
insert into tmp_tbl values (4, 'EUR', 'EUR-WEST', 'NL', 'NLZWO');
insert into tmp_tbl values (5, 'EUR', 'EUR-WEST', 'BE', 'BELEB');
insert into tmp_tbl values (6, 'EUR', 'EUR-WEST', 'BE', '');
insert into tmp_tbl values (7, 'EUR', 'EUR-WEST', 'NL', '');
insert into tmp_tbl values (8, 'EUR', 'EUR-WEST', '', '');

This is a list of codes where g1code = continent, g2code = region, g3code = country, g4code = city.

The rows with columns that have empty values are to be included if the rest matches.

So if I for example select row 4 then also want row 7 and row 8 to be included. As the empty values can match anything.

I created the following query for this:

SELECT DISTINCT
    t1.*,
    t2.*,
    t3.*,
    t4.*
FROM 
    tmp_tbl tm,
    (SELECT * FROM tmp_tbl WHERE g4code = 'NLZWO') g4
OUTER APPLY
    (SELECT * FROM tmp_tbl t 
     WHERE t.g1code = g4.g1code AND t.g2code = '' AND t.g3code = '' AND t.g4code = '') t1
OUTER APPLY
    (SELECT * FROM tmp_tbl t 
     WHERE t.g1code = g4.g1code AND t.g2code = g4.g2code AND t.g3code = '' AND t.g4code = '') t2
OUTER APPLY 
    (SELECT * FROM tmp_tbl t 
     WHERE t.g1code = g4.g1code AND t.g2code = g4.g2code AND t.g3code = g4.g3code AND t.g4code = '') t3
OUTER APPLY
    (SELECT * FROM tmp_tbl t 
     WHERE t.g1code = g4.g1code AND t.g2code = g4.g2code AND t.g3code = g4.g3code AND t.g4code = g4.g4code) t4

This does work but I only get 1 row and everything in columns like this:

gcode_id    g1code  g2code  g3code  g4code  gcode_id    g1code  g2code      g3code  g4code  gcode_id    g1code  g2code      g3code  g4code  gcode_id    g1code  g2code      g3code  g4code
NULL        NULL    NULL    NULL    NULL    8           EUR     EUR-WEST                    7           EUR     EUR-WEST    NL              4           EUR     EUR-WEST    NL      NLZWO

Is there a way to do this more efficiently or convert the columns to rows and then end up eventually with id 8, 7 and 4 (where the nulls are filtered out as they are not relevant)?

Thanks

Answer 1

Maybe could be more elegant and generic, but this works.

create table #tmp_tbl (
    gcode_id    int,
    g1code      varchar(20),
    g2code      varchar(20),
    g3code      varchar(20),
    g4code      varchar(20)
);

insert into #tmp_tbl values (1, 'AFR', 'AFR-EAST', 'BI', 'BIBJM');
insert into #tmp_tbl values (2, 'AFR', 'AFR-SOUTH', 'ZA', 'ZACPT');
insert into #tmp_tbl values (3, 'EUR', 'EUR-BRI_IS', 'GB', 'GBMAN');
insert into #tmp_tbl values (4, 'EUR', 'EUR-WEST', 'NL', 'NLZWO');
insert into #tmp_tbl values (5, 'EUR', 'EUR-WEST', 'BE', 'BELEB');
insert into #tmp_tbl values (6, 'EUR', 'EUR-WEST', 'BE', '');
insert into #tmp_tbl values (7, 'EUR', 'EUR-WEST', 'NL', '');
insert into #tmp_tbl values (8, 'EUR', 'EUR-WEST', '', '');
insert into #tmp_tbl values (9, 'EUR', '', '', '');

;
with main as (
    select *
    from #tmp_tbl
    where g4code = 'NLZWO'
), 
parent as (
    select m.gcode_id
    , m.g1code
    , m.g2code
    , m.g3code
    , m.g4code
    from main m

    union
    select p.gcode_id
    , p.g1code
    , p.g2code
    , p.g3code
    , p.g4code
    from #tmp_tbl p
      inner join main m on m.g3code = p.g3code
                       and m.g2code = p.g2code
                       and m.g1code = p.g1code
                       and p.g4code = ''
), 
grandparent as (
    select p.gcode_id
    , p.g1code
    , p.g2code
    , p.g3code
    , p.g4code
    from parent p

    union
    select gp.gcode_id
    , gp.g1code
    , gp.g2code
    , gp.g3code
    , gp.g4code
    from #tmp_tbl gp
      inner join parent p on p.g2code = gp.g2code
                         and p.g1code = gp.g1code
                         and gp.g3code = ''
), 
greatgrandparent as (
    select gp.gcode_id
    , gp.g1code
    , gp.g2code
    , gp.g3code
    , gp.g4code
    from grandparent gp

    union
    select ggp.gcode_id
    , ggp.g1code
    , ggp.g2code
    , ggp.g3code
    , ggp.g4code
    from #tmp_tbl ggp
      inner join grandparent gp on gp.g1code = ggp.g1code
                               and ggp.g2code = ''
)

select *
from greatgrandparent

Or were you really looking for it to all be on one line?

select *
from #tmp_tbl m
  inner join #tmp_tbl p on p.g3code = m.g3code
                       and p.g2code = m.g2code
                       and p.g1code = m.g1code
                       and p.g4code = ''
  inner join #tmp_tbl gp on gp.g2code = p.g2code
                        and gp.g1code = p.g1code
                        and gp.g3code = ''
  inner join #tmp_tbl ggp on ggp.g1code = gp.g1code
                         and ggp.g2code = ''

where m.g4code = 'NLZWO'

Answer 2

Are you looking for or?

select t.*
from tmp_tbl t join
     tmp_tbl t2
     on (t.g1code = t2.g1code or t.g1code = '') and
        (t.g2code = t2.g2code or t.g2code = '') and
        (t.g3code = t2.g3code or t.g3code = '') 
where t2.g4code = 'NLZWO';

Here is a db<>fiddle.