Union of Columns

Question

I have some SQL queries that retrun the following.

• Total Pre Orders Web
• Total Pre Orders Phone
• Total Orders Web
• Total Orders phone

Each one of those queries retrurns a the query name and the count of the query. The query name ahs been addded as just text in the select part.

If I just want to return all the pre orders I can do

Total Pre Orders Web
UNION
Total Pre Orders Phone

This will return

Total Pre Orders Web    | 10
Total Pre Orders Phone  | 2

How would I go about doing something like

Total Pre Orders Web    | 10 | Total Orders Web    | 20
Total Pre Orders Phone  | 2  | Total Orders Phone  | 0

SO I want to do a Union to the side?

Answer 1

If I understand you correctly you have four different queries and you want the result from two of them on the first row and the other two on the second row.

You can do sub-queries something like this:

select 'Total Pre Orders Web',
       (select 10 /*Replace this with your query*/), 
       'Total Orders Web',
       (select 20 /*Replace this with your query*/)
union all
select 'Total Pre Orders Phone',
       (select 2 /*Replace this with your query*/),
       'Total Orders Phone',
       (select 0 /*Replace this with your query*/)

Result:

---------------------- ----------- ------------------ -----------
Total Pre Orders Web   10          Total Orders Web   20
Total Pre Orders Phone 2           Total Orders Phone 0

Answer 2

It's called a JOIN. Take this example scenario for instance:

create table MediaType
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

insert into MediaType(Name)
select 'Web'
union all
select 'Phone'
go

create table Orders
(
    id int identity(1, 1) not null,
    Name varchar(100) not null,
    fkMediaType int foreign key references MediaType(id),
    TotalOrders int not null
)
go

insert into Orders(Name, fkMediaType, TotalOrders)
select 'First Orders', 1, 10
union all
select 'Second Orders', 2, 20
go

create table PreOrders
(
    id int identity(1, 1) not null,
    Name varchar(100) not null,
    fkMediaType int foreign key references MediaType(id),
    TotalOrders int not null
)
go

insert into PreOrders(Name, fkMediaType, TotalOrders)
select 'First PreOrders', 1, 0
union all
select 'Second PreOrders, 2, 2
go

Then to get your data, you'd do something like this:

select
    o.Name + ' : ' + m.Name,
    o.TotalOrders,
    p.Name + ' : ' + m.Name,
    p.TotalOrders
from Orders o
inner join PreOrders p
on o.fkMediaType = p.fkMediaType
inner join MediaType m
on o.fkMediateType = m.id