CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jay
Jay

Reputation: 465

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:
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!

Upvotes: 1

Views: 1537

Answers (4)

sheeni
sheeni

Reputation: 407

Try this;

SELECT a.Category,c.SubCategory,ISNULL(b.Value,0) AS Value
FROM MyTable a 
CROSS JOIN Mytable c
LEFT JOIN MyTable b
On c.SubCategory = b.SubCategory
AND a.category = b.Category
GROUP BY   a.Category,c.SubCategory,b.Value

Upvotes: 0

ander2ed
ander2ed

Reputation: 1338

This is what I would use. Similar to the others, but in my mind a bit easier to read and understand. Also avoids using a CTE, which I believe is unnecessary for this. I also prefer group by over distinct in most cases, but again, just personal preference.

SELECT a.*, Value = ISNULL(b.Value, 0)
FROM (  -- returns all pairs of category and subcategory
    SELECT *
    FROM (
         SELECT Category
         FROM MyTable
         GROUP BY Category
    ) a
    CROSS JOIN (
        SELECT SubCategory 
        FROM MyTable
        GROUP BY SubCategory
    ) b
) a
LEFT JOIN MyTable b
ON a.Category = b.Category and a.SubCategory = b.SubCategory

Alias the subqueries however you wish - I was lazy and used a and b

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40289

Here's another version.

The performance of whichever version you go with will be impacted if your table is large (but only if it can be measured in 100s of megabytes, I'd guess)

;WITH cteGaps as (
    select
       cat.Category
      ,subcat.SubCategory
     from (--  All categories
           select distinct Category
            from MyTable
          ) cat
      cross join (--  All subcategories
                  select distinct SubCategory
                   from MyTable
                 ) subcat
    except select
       Category
      ,SubCategory
     from MyTable
    )
INSERT MyTable (Category, SubCategory, Value)
 select Category, SubCategory, 0
  from cteGaps

Upvotes: 0

Mahendra
Mahendra

Reputation: 51

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

Upvotes: 2

Related Questions