CODEWITHSUNDEEP
sqlsql-server
hotseetotsee
hotseetotsee

Reputation: 71

CASE WHEN rownum() return rows

I have a family_expenses table that looked like below:

family_id    bank_code    amount    year   month
  001          LB         130.00    2017     1
  001          MB         200.00    2017     1
  001          CB          78.00    2017     2
  001          SB          69.00    2017     3
  009          LB          78.00    2017     1

The table above have some other several columns.

What I need to do is get all the records for a family id and combine it with my another sub-query from another table. I did some query using CASE WHEN.
My query is as below:

 SELECT
(CASE WHEN t.rownum = 1 THEN t.bank_code ELSE '' END
) as bank_code1,
(CASE WHEN t.rownum = 1 THEN t.amount ELSE 0.00 END
) as amt1,
(CASE WHEN t.rownum = 2 THEN t.bank_code ELSE '' END
) as bank_code2,
(CASE WHEN t.rownum = 2 THEN t.amount ELSE 0.00 END
) as amt2,
(CASE WHEN t.rownum = 3 THEN t.bank_code ELSE '' END
) as bank_code3,
(CASE WHEN t.rownum = 3 THEN t.amount ELSE 0.00 END
) as amt3
FROM
(
select  
ROW_NUMBER() OVER(partition by family_id Order by family_id, bank_code) 
as rownum, 
family_id, bank_code, AMOUNT
from family_expenses
where year = 2017 and month = 1
and family_id= 001
) t

The result display 2 rows of records.

bank_code1     amt1       bank_code2   amt2    bank_code3     amt3
   LB         130.00
                             MB       200.00

How can I make the output display all columns show result in a single row?

Expected output:

bank_code1     amt1       bank_code2   amt2    bank_code3     amt3
   LB         130.00         MB       200.00

Upvotes: 0

Views: 3667

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

Try it like this (just a wee modification of your original code)...

IF OBJECT_ID('tempdb..#family_expenses', 'U') IS NOT NULL 
DROP TABLE #family_expenses;

CREATE TABLE #family_expenses (
    family_id CHAR(3) NOT NULL,
    bank_code CHAR(2) NOT NULL,
    amount MONEY NOT NULL,
    [year] INT NOT NULL,
    [month] TINYINT NOT NULL 
    );
INSERT #family_expenses (family_id, bank_code, amount, [year], [month]) VALUES
    ('001', 'LB', 130.00, 2017, 1),
    ('001', 'MB', 200.00, 2017, 1),
    ('001', 'CB', 78.00, 2017, 2),
    ('001', 'SB', 69.00, 2017, 3),
    ('009', 'LB', 78.00, 2017, 1);

SELECT
    t.family_id,
    bank_code1 = MAX(CASE WHEN t.rownum = 1 THEN t.bank_code END ),
    amt1 = SUM(CASE WHEN t.rownum = 1 THEN t.amount END),
    bank_code2 = MAX(CASE WHEN t.rownum = 2 THEN t.bank_code END ),
    amt2 = SUM(CASE WHEN t.rownum = 2 THEN t.amount END ),
    bank_code3 = MAX(CASE WHEN t.rownum = 3 THEN t.bank_code END ),
    amt3 = SUM(CASE WHEN t.rownum = 3 THEN t.amount ELSE 0.00 END )
FROM
    (   
    SELECT
        rownum = ROW_NUMBER() OVER (PARTITION BY fe.family_id ORDER BY fe.family_id, fe.bank_code ),
        fe.family_id,
        fe.bank_code,
        fe.amount
    FROM
        #family_expenses fe
    WHERE
        fe.year = 2017
        AND fe.month = 1
        AND fe.family_id = '001'
    ) t
GROUP BY
    t.family_id;

results...

family_id bank_code1 amt1                  bank_code2 amt2                  bank_code3 amt3
--------- ---------- --------------------- ---------- --------------------- ---------- ---------------------------------------
001       LB         130.00                MB         200.00                NULL       0.0000

Upvotes: 1

Related Questions