I have two tables:
Table 1:

Number| Code | Value
Garden |A0,C2 | 100
Garden |rest  | 500
House  |A0,C2 | 100
House  |rest  | 500


|A0  |
|B1  |
|C2  |
|D3  |
|E4  |

I would like to get a Table that looks something like this:

Number| Code | Value
Garden |A0    | 100
Garden |B1    | 500
Garden |C2    | 100
Garden |D3    | 500
Garden |E4    | 500
House  |A0    | 100
House  |B1    | 500
House  |C2    | 100
House  |D3    | 500
House  |E4    | 500

Does anyone know a SQL Statement on how I can get to this table

Changing either table 1 or table 2 is not possible

2 Answers

Gordon Linoff On

You should fix the table1 so it is not using lists represented as comma-delimited strings. You should have a separate table for this. There are many good reasons for this.

Assuming that you are stuck with someone-else's really, really bad data model, you can use a join. There are several approaches. SQL Server 2017+ has built-in string_split() function.

Then, you want to generate the rows with a cross join and bring in the values you want using left joins:

select n.number, t2.code, coalesce(t1.value, t1rest.value) as value
from (select distinct number from table1) n cross join
     table2 t2 left join
     table1 t1 
     on t1.number = n.number and ',' + t1.codes + ',' like '%,' + t2.code + ',%' left join
     table1 t1rest
     on t1rest.number = n.number and t1rest.codes = 'rest';

Here is a db<>fiddle

Larnu On

I'm not a fan of this solution, as the JOIN with an EXISTS is ugly. I strongly suggest fixing your data model here. You shouldn't be using delimited data in your table, and you shouldn't be using a value like 'rest' to denote you want all other values other than those previously defined. The result set you get here is a normalised dataset, and that is exactly how you should be storing your data:

CREATE TABLE Table1 (Number varchar(6), --A number is a varchar?
                     Code varchar(50),
                     [Value] int);

INSERT INTO dbo.Table1 (Number,
VALUES ('Garden','A0,C2',100),

CREATE TABLE Table2 (Code char(2));
INSERT INTO dbo.Table2 (Code)
VALUES ('A0'),

    SELECT T1.Number,
           SS.[value] AS Code,
    FROM dbo.Table1 T1
         CROSS APPLY STRING_SPLIT(T1.Code, ',')  SS)
SELECT C.Number,
     JOIN dbo.Table2 T2 ON C.Code = T2.Code
                        OR (C.Code = 'rest'
                       AND  NOT EXISTS (SELECT 1
                                        FROM CTE e
                                        WHERE e.Number = C.Number
                                          AND e.Code = T2.Code))


DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;                       


If you aren't using SQL Server 2016+, you won't be able to use STRING_SPLIT. As a result I suggest looking up an "XML Splitter" or delimitedsplit8k(_lead).