Show same columns with different values in same row

Question

I have 3 tables, this is Sql Fiddle Demo http://www.sqlfiddle.com/#!15/89ac5/3/0

create table entities (id int,  credit int,   debit  int, value  int,etype int, date date);

insert into entities values 
    (1,101,100,5000,1,'01/01/2014'),
    (1,101,100,1000,2,'01/01/2014'),
    (1,102,100,2000,1,'01/01/2014'),
    (1,102,100,4000,2,'01/01/2014');

create table accounts (id int,  name varchar(20));

insert into accounts values 
    (100,  'Clinic'),
    (101,  'Mark'),
    (102,  'Jone');

create table etype (id int, name varchar(20));

insert into etype values
    (1, 'Medicine'),
    (2, 'Diagnoise');

---

when i run this query :

select 
    e.id, 
    credit_account.name as CreditName, 
    debit_account.name as DebitName,
    t.name,    
    e.date 
from entities e
join accounts as credit_account on e.credit = credit_account.id
join accounts as debit_account  on e.debit  = debit_account.id
Join etype as t on e.etype = t.id

---

I have this result:

ID  CREDITNAME  DEBITNAME   VALUE   NAME    DATE
1          Mark     Clinic  5000    Medicine    January, 01 2014 00:00:00+0000
2          Mark     Clinic  1000    Diagnoise   January, 01 2014 00:00:00+0000
3          Jone     Clinic  2000    Medicine    January, 01 2014 00:00:00+0000
4          Jone     Clinic  4000    Diagnoise   January, 01 2014 00:00:00+0000

---

finally, I want a view to show this result:

ID  CREDITNAME  DEBITNAME   Medicine    Diagnoise   DATE
1          Mark     Clinic  5000        1000        January, 01 2014 00:00:00+0000
2          Jone     Clinic  2000        4000        January, 01 2014 00:00:00+0000

if we can make it Dynamically, example if we add 'Lab'

Answer 1

You can use conditional aggregation:

select row_number() over (order by creditname, debitname) as id, creditname, debitname,
       sum(case when name = 'Medicine' then value end) as medicine,
       sum(case when name = 'Diagnoise' then value end) as diagnoise,
       date
from table t 
group by creditname, debitname, date;

EDIT:

Based on your query:

select row_number() over (order by creditname, debitname) as id, 
       credit_account.name as CreditName, 
       debit_account.name as DebitName,
       sum(case when t.name = 'Medicine' then e.value end) as medicine,
       sum(case when t.name = 'Diagnoise' then e.value end) as diagnoise,
       e.date
from entities join
     accounts credit_account
     on e.credit = credit_account.id join
     accounts debit_account 
     on e.debit  = debit_account.id Join
     etype t
     on e.etype = t.id
group by credit_account.name, debit_account.name, e.date