Jay - 9 months ago 38

SQL Question

Looking for some help on this. Lets my dataset has 3 columns: Category, SubCategory and Value.

Each Category does NOT contain every SubCategory, and if it does NOT, I want to add a new row / placeholder for that missing Category/SubCategory combination with a value of 0.

For example:

`SELECT distinct(SubCategory) From MyTable returns:`

SubCategory-A

SubCategory-B

SubCategory-C

SubCategory-D

Let's say Category1 contains SubCategory-A and SubCategory-C. I want to add placeholders/new rows for the missing SubCategories B and D, so ultimately my results would look like (order doesn't matter):

`Category1 SubCategory-A 100`

Category1 SubCategory-C 125

Category1 SubCategory-B 0 << new row / placeholder

Category1 SubCategory-D 0 << new row / placeholder

I have multiple Categories I'd like to apply this to. Can anyone help me with this? Much appreciated!

Answer

Try this out:

First insert All distinct categoryId and subcategoryid into a temp table from the target table

```
SELECT DISTINCT CategoryIds, SubCategoryIds into #t1 FROM TableC
```

Then Insert the values into Target table

```
INSERT INTO TableC(CategoryIds, SubCategoryIds,Value)
SELECT CategoryIds, SubCategoryIds,0 FROM
(SELECT Distinct CategoryIds From TableA) A
CROSS JOIN (SELECT Distinct SubCategoryIds From TableA) B
EXCEPT
SELECT CategoryIds, SubCategoryIds, 0 FROM #t1
```

Source (Stackoverflow)