plz help me to solve this query.

Database
N/A

plz help me to solve this query.

hi all,
plz help me to solve this query.

create table ninjar.henle
(
system varchar(20),
loan_bal decimal(12,2),
dead_bal decimal(12,2),
xxxx_bal decimal(12,2),
yyyy_bal decimal(12,2),
zzzz_bal decimal(12,2)
);

insert into ninjar.henle(system,loan_bal,dead_bal,xxxx_bal,yyyy_bal,zzzz_bal ) values('cc3',2300.34, 5656.36, 4567.56, 4542.12, 4099.45);

insert into ninjar.henle(system,loan_bal,dead_bal,xxxx_bal,yyyy_bal,zzzz_bal ) values('cc2',4545.45, 7878.36, 1232.56, 23.67, 9711.87);

sel * from ninjar.henle;
-->
system loan_bal dead_bal xxxx_bal yyyy_bal zzzz_bal
----------------------------------------------------------
cc2 4545.45 7878.36 1232.56 23.67 9711.87
cc3 2300.34 5656.36 4567.56 4542.12 4099.45

The output i want is :
subject cc2_bal cc3_bal
-------------------------------
dead_bal 7878.36 5656.36
loan_bal 4545.45 2300.34
xxxx_bal 1232.56 4567.56
yyyy_bal 23.67 4542.12
zzzz_bal 9711.87 4099.45

How I write the SQL, Plz suggest me.

Thanks in advance.

Regards:
Ninjar
1 REPLY
Enthusiast

Re: plz help me to solve this query.

Not sure if there's an easier way... but this is how I'd do it. (Replace table name with yours)

SELECT
'dead_bal' as subject,
SUM(CASE WHEN system = 'cc2' THEN dead_bal END) as cc2_bal,
SUM(CASE WHEN system = 'cc3' THEN dead_bal END) as cc3_bal
FROM TEST_RTIDB.jw_test

UNION ALL
SELECT
'loan_bal' as subject,
SUM(CASE WHEN system = 'cc2' THEN loan_bal END) as cc2_bal,
SUM(CASE WHEN system = 'cc3' THEN loan_bal END) as cc3_bal
FROM TEST_RTIDB.jw_test

UNION ALL
SELECT
'xxxx_bal' as subject,
SUM(CASE WHEN system = 'cc2' THEN xxxx_bal END) as cc2_bal,
SUM(CASE WHEN system = 'cc3' THEN xxxx_bal END) as cc3_bal
FROM TEST_RTIDB.jw_test

UNION ALL
SELECT
'yyyy_bal' as subject,
SUM(CASE WHEN system = 'cc2' THEN yyyy_bal END) as cc2_bal,
SUM(CASE WHEN system = 'cc3' THEN yyyy_bal END) as cc3_bal
FROM TEST_RTIDB.jw_test

UNION ALL
SELECT
'zzzz_bal' as subject,
SUM(CASE WHEN system = 'cc2' THEN zzzz_bal END) as cc2_bal,
SUM(CASE WHEN system = 'cc3' THEN zzzz_bal END) as cc3_bal
FROM TEST_RTIDB.jw_test