SQLTeam.com | Weblogs | Forums

Adding Data to a Query

I don't know how to even google the answer to this. I am in bit of need for some assistance.

I have data that looks like this:

Cat 1 brown
Cat 2 blue
Cat 3 purple
Dog 1 black
mouse 1 blue
mouse 2 yellow
mouse 3 red
mouse 4 green

I need my result set to add data until the increment equals 5 as shown below:
Cat 1 brown
Cat 2 blue
Cat 3 purple
Cat 4 NULL
Cat 5 NULL
Dog 1 black
Dog 2 NULL
Dog 3 NULL
Dog 4 NULL
Dog 5 NULL
mouse 1 blue
mouse 2 yellow
mouse 3 red
mouse 4 green
mouse 5 NULL

Any assistance would be helpful, this is my first post.

For future reference - when asking for help please provide sample data in the form of a create/declare table and insert statements. Here I have used a table variable to provide sample data.

 --==== Create some sample data
Declare @myTable Table (col1 varchar(10), col2 int, col3 varchar(10));
 Insert Into @myTable (col1, col2, col3)
 Values ('Cat', 1, 'Brown')
      , ('Cat', 2, 'Blue')
      , ('Cat', 3, 'Purple')
      , ('Dog', 1, 'Black')
      , ('Mouse', 1, 'Blue')
      , ('Mouse', 2, 'Yellow')
      , ('Mouse', 3, 'Red')
      , ('Mouse', 4, 'Green');

 --==== Solution
   With allCategories (col1, col2)
     As (
 Select Distinct
      , t.n
   From @myTable                mt
  Cross Join (Values (1), (2), (3), (4), (5)) As t(n)
 Select ac.col1
      , ac.col2
      , mt.col3
   From allCategories           ac
   Left Join @myTable           mt On mt.col1 = ac.col1
                                  And mt.col2 = ac.col2;

To create a code-block, start with three back-ticks followed by the code and end with another three back-ticks.

For this solution - the first step is to create the list of distinct col1 and col2 for the full set (all 5 numbers). Then we just left join in the original table - joining on col1 and col2. Return the values from col1 and col2 from the full set - and only those values that match from the original table.


Hey, thank you so much