Jay Jay - 11 months ago 45
SQL Question

SQL Adding rows to my dataset as placeholders for missing groups?

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:

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 Source

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 
SELECT CategoryIds, SubCategoryIds, 0 FROM #t1