CODEWITHSUNDEEP
sqlsql-server
froodo
froodo

Reputation: 87

JOIN 2 select queries FROM ONE TABLE

i have a single table called journal. Journal has 4 columns:

  1. Date (date)
  2. Account(int)
  3. Type(varchar(1) (Can accept chars D or C)
  4. Amount(decimal(18,2))

JOURNAL

Date         Account    Amount    Type
2012-05-31   20001      300       D
2012-05-31   20002      700       C
2012-05-31   20003      600       D
2012-05-31   20004      900       C

The type column can only take 2 types of character values: D or C. So i need a query that will give me 4 columns, such that the resulting columns are:

Date        Account       D       C
2012-05-31  200101        300     0
2012-05-31  200102        0       700
2012-05-31  200103        600     0
2012-05-31  200104        0       900

with the D and C columns filled with values from Amount, whether they are null or not.

Upvotes: 1

Views: 292

Answers (3)

Taryn
Taryn

Reputation: 247690

You can accomplish this with a PIVOT:

create table journal
(
    date datetime,
    account int,
    amount money,
    type varchar(1)
)

insert into journal values ('05/31/12', 20001, 300, 'D')
insert into journal values ('05/31/12', 20002, 700, 'C')
insert into journal values ('05/31/12', 20003, 600, 'D')
insert into journal values ('05/31/12', 20004, 900, 'C')

select date
    , account
    , isnull([D], 0) as D
    , isnull([C], 0) as C
from 
(
    select *
    from journal
) x
pivot
(
    sum(amount)
    for type in ([D], [C])
) p

drop table journal

See a SQL Fiddle with a Demo

If you want to join the account names, then you will want to perform a JOIN on that table. See update SQL Fiddle with a demo:

select date
    , account
    , name
    , isnull([D], 0) as D
    , isnull([C], 0) as C
from 
(
    select *
    from journal j
    inner join account a
      on j.account = a.id
) x
pivot
(
    sum(amount)
    for type in ([D], [C])
) p

Upvotes: 3

mcha
mcha

Reputation: 2998

SELECT DATE,
       account,
       CASE
         WHEN TYPE = 'D' THEN
          amount
         ELSE
          0
       END D,
       CASE
         WHEN TYPE = 'C' THEN
          amount
         ELSE
          0
       END C TYPE
  FROM JOURNAL

Upvotes: 1

Matthew
Matthew

Reputation: 10444

Are you trying to do something like this?

SELECT
    SUM(CASE [Type] WHEN 'C' THEN [Amount] ELSE 0 END) AS C
    ,SUM(CASE [Type] WHEN 'D' THEN [Amount] ELSE 0 END) AS D
    ,[Date]
    ,[Account]
FROM
    Journal
GROUP BY
    [Date]
    ,[Account]

This will sum each value's amount for the date/account combination. You could replace the SUM aggregate with any aggregate of your choice (MIN, MAX etc)

Upvotes: 0

Related Questions