sql server - Table data into Pivot -

an accounting table has sample data shown below: (there more acctgheads shown here)

 +-------+-----------+--------+-----+ | loan  | acctghead | amount | d_c | +-------+-----------+--------+-----+ |     1 | principal |  10000 | d   | |     1 | principal |    500 | c   | |     1 | cash      |  10000 | c   | |     1 | cash      |    500 | d   | |     2 | principal |   5000 | d   | |     2 | cash      |   5000 | c   | |     2 | cash      |    300 | d   | |     2 | principal |    300 | c   | |     1 | intdue    |     50 | d   | |     1 | intincome |     50 | c   | +-------+-----------+--------+-----+ 

the desired ouput is:

 +------+-------------+-------------+--------+--------+----------+----------+-------------+-------------+ | loan | principal_d | principal_c | cash_d | cash_c | intdue_d | intdue_c | intincome_d | intincome_c | +------+-------------+-------------+--------+--------+----------+----------+-------------+-------------+ |    1 |       10000 |         500 |    500 |  10000 |       50 |        0 |           0 |          50 | |    2 |        5000 |         300 |    300 |   5000 |        0 |        0 |           0 |           0 | +------+-------------+-------------+--------+--------+----------+----------+-------------+-------------+ 

what query accomplish this?

thanks in advance help.

try this: (assuming acctghead has fixed number of values shown)

select loan,  isnull(principal_d,0) principal_d, isnull(principal_c,0) principal_c, isnull(cash_d,0) cash_d, isnull(cash_c,0) cash_c, isnull(intdue_d,0) intdue_d, isnull(intdue_c,0) intdue_c, isnull(intincome_d,0) intincome_d, isnull(intincome_c,0) intincome_c  (select loan, amount, acctghead + '_' + d_c acctgheaddc t) t pivot (        max(amount) acctgheaddc in        (principal_d,principal_c,cash_d,cash_c,         intdue_d,intdue_c,intincome_d,intincome_c) ) p 

sql demo