Filter with partition SQL

Question

I have this data as a tree:

Id	name	t1	t2	t3	fullT
1	A	59	null	null	59
2	B	59	20	null	59.02
3	C	59	40	null	59.04
4	D	59	60	null	59.06
5	E	59	60	01	59.060.01
6	F	59	60	10	59.060.10

as I mentioned the table is a tree so t1 is the parent and t2 is the first child and t3 is the second child. so if the t2 and t3 are null means it is the parent.

                     59
    /                |              \
   20               40              60
                                   /  \
                                 01   10

and this is the result I expect:

Id	name	t1	t2	t3	fullT
1	A	59.A	null	null	59
2	B	59.A	020.B	null	59.02
3	C	59.A	040.C	null	59.04
4	D	59.A	060.D	null	59.06
5	E	59.A	060.D	01.E	59.060.01
6	F	59.A	060.D	10.F	59.060.10

to make it readable I want to concatenate between each (t) and its name as shown in the previous table, so I have created this query.

SELECT 
    Id,
    name,
    MAX(CASE WHEN t2 = '' AND t3 = '' THEN CONCAT([t1],'.', [ICSAr]) ELSE null END) OVER (PARTITION BY t1) AS t1,
    MAX(CASE WHEN t1 != '' AND t3 = ''  AND t2 != '' THEN CONCAT([t2],'.', [name]) ELSE NULL END) OVER (PARTITION BY t1,[t2]) AS t2,
    CASE WHEN [t3] != '' THEN CONCAT([t3],'.', [name]) END AS [t3]
FROM [table]

but if I use a where condition based on t2 or t3, the result is not correct because it gives the result based on the condition. ex
1- WHERE t1= 59 and t2 = 020

Id	name	t1	t2	t3
2	B	null	020.B	null

the result I expect is:

Id	name	t1	t2	t3
2	B	59.A	020.B	null

2- WHERE t1= 59 and t3 = 10

Id	name	t1	t2	t3
6	F	Null	Null	10.F

the result I expect is:

Id	name	t1	t2	t3
6	F	59.A	060.D	10.F

how can I make the query over all the tables even if there are conditions?

Answer 1

Assuming SQL Server, you can use FIRST_VALUE() as NULLs are ordered first.

• Different DBMSes order NULLs differently
• So, specifying your DBMS is crucial

It's similar to using MAX(CASE WHEN x IS NULL THEN name END) in a window.

Both versions are below...

• Just looking up the names, not the concatenations...

CREATE TABLE your_table (
  id                INT,
  name              CHAR,
  t1                INT,
  t2                INT,
  t3                INT,
  fullT             VARCHAR(11)
)

INSERT INTO
  your_table
VALUES
  (1, 'A', 59,  null, null, '59'),
  (2, 'B', 59,  20,   null, '59.02'),
  (3, 'C', 59,  40,   null, '59.04'),
  (4, 'D', 59,  60,   null, '59.06'),
  (5, 'E', 59,  60,   01,   '59.060.01'),
  (6, 'F', 59,  60,   10,   '59.060.10')

WITH
  name_lookup AS
(
  SELECT
    id,
    name,
    t1, t2, t3,
    FIRST_VALUE(name) OVER (PARTITION BY t1         ORDER BY t1, t2, t3)  AS name1,
    FIRST_VALUE(name) OVER (PARTITION BY t1, t2     ORDER BY t1, t2, t3)  AS name2,
    FIRST_VALUE(name) OVER (PARTITION BY t1, t2, t3 ORDER BY t1, t2, t3)  AS name3
  FROM
    your_table
)
SELECT
    id,
    name,
    t1, t2, t3,
    CASE WHEN t1 IS NOT NULL THEN name1 END   AS name1,
    CASE WHEN t2 IS NOT NULL THEN name2 END   AS name2,
    CASE WHEN t3 IS NOT NULL THEN name3 END   AS name3
FROM
  name_lookup
ORDER BY
  id

id	name	t1	t2	t3	name1	name2	name3
1	A	59	null	null	A	null	null
2	B	59	20	null	A	B	null
3	C	59	40	null	A	C	null
4	D	59	60	null	A	D	null
5	E	59	60	1	A	D	E
6	F	59	60	10	A	D	F

WITH
  name_lookup AS
(
  SELECT
    id,
    name,
    t1, t2, t3,
    MAX(CASE WHEN t2 IS NULL THEN name ELSE NULL END) OVER (PARTITION BY t1        )  AS name1,
    MAX(CASE WHEN t3 IS NULL THEN name ELSE NULL END) OVER (PARTITION BY t1, t2    )  AS name2,
    MAX(                          name              ) OVER (PARTITION BY t1, t2, t3)  AS name3
  FROM
    your_table
)
SELECT
    id,
    name,
    t1, t2, t3,
    CASE WHEN t1 IS NOT NULL THEN name1 END   AS name1,
    CASE WHEN t2 IS NOT NULL THEN name2 END   AS name2,
    CASE WHEN t3 IS NOT NULL THEN name3 END   AS name3
FROM
  name_lookup
ORDER BY
  id

id	name	t1	t2	t3	name1	name2	name3
1	A	59	null	null	A	null	null
2	B	59	20	null	A	B	null
3	C	59	40	null	A	C	null
4	D	59	60	null	A	D	null
5	E	59	60	1	A	D	E
6	F	59	60	10	A	D	F

fiddle