SQL creating a column of alternating values based on a condition

Question

Hi, I would like to create a query that returns rows in the following format:

string name, int period, double value, string category

where there are two sets of data which I want to have the same name and period, the value is arbitrary, and the category should be 1 or 2. The category data does not exist and needs to be 'created' within the query.

The two sets of data will come from separate tables, A and B which return:

name, period, value

So I want to put a string '1' in the category column if it is from A, '2' if it is from B and return the normal data in the other columns.

How could I do this?

Answer 1

The way I'm reading your question, you're doing a union, and you want to see which value is in table A, and which is in table B. If that's the case, then creating your category is very easy, as you just specify the value you want for the table you're in. Like so:

select
    name,
    period,
    value,
    '1' as category
from
    A
union all
select
    name,
    period,
    value,
    '2' as category
from
    B
order by
    name, period, category

Answer 2

Use UNION ALL

SELECT  name, 
        period, 
        value, 
        '1' AS category
FROM    tableA
UNION ALL
SELECT  name, 
        period, 
        value, 
        '2' AS category
FROM    tableB