Inserting new column with using select and union all

Question

I have a product table.

Create Table tblProduct
(
    ID int,
    Project_No int,
    OrderDate date
)

insert into tblProduct values (2580, 100, '2012-01-13')
insert into tblProduct values (2581, 101, '2012-01-21')
insert into tblProduct values (2582, 102, '2012-03-04')
insert into tblProduct values (2583, 103, '2012-02-14')

I need to combine with months and give an output as follows based on the order date (but i don't want to display in output result):

Jan   2580   100
Feb   2581   101
Mar   2582   102

But I don't have separate table for month.

So, I think I have to use SELECT.....UNION ALL to achieve what I want.

But I don't know how to use. Can someone shed some light on this?

Answer 1

Thanks guys for your help. I've finally figured it out and achieve exactly what I want.

SELECT '1'As Mnth, ID as ProductID,Project_No,OrderDate
from T2
where month(OrderDate)=1
UNION 
select '2'As Mnth, ID as ProductID,Project_No,OrderDate
from T2
where month(OrderDate) =2
UNION 
select '3'As Mnth, ID as ProductID,Project_No,OrderDate
from T2
where month(OrderDate) =3
UNION 
select '4'As Mnth, ID as ProductID,Project_No,OrderDate
from T2
where month(OrderDate) =4

Here, I don't have to create a separate table for Month, and also I don't need any table to create in advance , to get the result.

Results

Answer 2

I thing you can get the desired output without using the UNION ALL operator.

Executed your queries into my SSMS2014

create table tblProduct(
    ID int,
    Project_No int,
    OrderDate datetime
)

insert into tblProduct
values
(2581,100,'2012-01-13'),
(2581,101,'2012-01-21'),
(2582,102,'2012-03-04'),
(2583,103,'2012-02-14')

select * from tblProduct

select 
    LEFT(DATENAME(month,OrderDate),3) as DateColumn,
    ID,
    Project_No 
from tblProduct

More Reading about DATENAME

Answer 3

Just use:

SELECT
  DATENAME(month, OrderDate), ID, Project_No
FROM tblProduct