Filter with partition SQL

I have this data as a tree:

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:

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

the result I expect is:

2- WHERE t1= 59 and t3 = 10

the result I expect is:

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

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

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

fiddle