CODEWITHSUNDEEP
sqlsql-server-2008
Micah Armantrout
Micah Armantrout

Reputation: 7001

Make Two Queries into 1 result set with 2 columns

Say I have a table that looks like this:

Person Table

ID    AccountID  Name
1        6       Billy  
2        6       Joe
3        6       Tom
4        8       Jamie
5        8       Jake
6        8       Sam

I have two queries that I know work by themselves:

Select Name Group1 from person where accountid = 6

Select Name Group2 from person where accountid = 8

But I want a single Result Set to look like this:

Group1   Group2

Billy    Jamie   
Joe      Jake
Tom      Same

Upvotes: 0

Views: 1856

Answers (2)

Taryn
Taryn

Reputation: 247860

You can use row_number() to assign a distinct value for each row, ans then use a FULL OUTER JOIN to join the two subqueries:

select t1.group1,
  t2.group2
from
(
  select name group1,
    row_number() over(order by id) rn
  from yourtable
  where accountid = 6
) t1
full outer join
(
  select name group2,
    row_number() over(order by id) rn
  from yourtable
  where accountid = 8
) t2
  on t1.rn = t2.rn;

See SQL Fiddle with Demo

Upvotes: 3

Andomar
Andomar

Reputation: 238296

I agree you should do this client side. But it can be done in T/SQL:

select  G1.Name as Group1
,       G2.Name as Group2
from    (
        select  row_number() over (order by ID) as rn
        ,       *
        from    Group
        where   AccountID = 6
        ) as G1
full outer join
        (
        select  row_number() over (order by ID) as rn
        ,       *
        from    Group
        where   AccountID = 8
        ) as G2
on      G1.rn = G2.rn
order by
        coalesce(G1.rn, G2.rn)

Upvotes: 3

Related Questions