SQL Getting Data with extra row

I have two tables with columns of my interests as Table1.Column1 and Table2.Column2 Table1 is Kind of a Group Table and Table2 is items table. The join query of these two tables gets the data in the followinf format

Column1   Column2
A         1
A         2
B         1
B         2
B         3

What开发者_StackOverflow社区 I want is to get data in the following format:

Column1   Column2
A         0
A         1
A         2
B         0
B         1
B         2
B         3

i.e. getting extra 0 for each group at the start each time. The 0 does not exits in the database.

Does anyone know how to achive this in SQL?

Many Thanks,

This is one way to do it.

SELECT  DISTINCT Column1, [Column2] = 0
FROM    (
          YourOriginalQuery
        ) q
UNION ALL 
        YourOriginalQuery

Most likely, there are better solutions by incorporating this requirement into your original query. If you post your query, we can come up with better alternatives.

Or something like:

select C.CategoryId, drv.CategoryGroupId from Category as C
cross join (
    select 0 as CategoryGroupId 
    UNION
    select CG.CategoryGroupId from CategoryGroup as CG
)drv order by CategoryId, CategoryGroupId