How can I get the multiple columns in a single row using MySQL?

Question

I want combine mutiple rows columns as single record in MySQL

For eg: Actual Data

--------------------------------------------------------------
     id         name          Loantype        Amount
--------------------------------------------------------------
     1          ABC              1            500000
     2          ABC              2            3500000
     3          XYZ              1            250000
     4          XYZ              2            2500000

I tried with the following query

SELECT
    id,
    (
        CASE Loantype
            WHEN 1 THEN Amount
            ELSE NULL
        END
    ) AS PersonalLoan,
    (
        CASE Loantype
            WHEN 2 THEN Amount
            ELSE NULL
        END
    ) AS HomeLoan
FROM
    customer
WHERE
    name = 'ABC'

but result comes as below

--------------------------------------------------------------
     id         name          PersonalLoan    HomeLoan
--------------------------------------------------------------
     1          ABC            500000         NULL
     1          ABC            NULL         2500000

Expected Result set

--------------------------------------------------------------
     id         name          PersonalLoan    HomeLoan
--------------------------------------------------------------
     1          ABC            500000         3500000

What happens if the same customer has multiple loans of the same LoanType? Or is there a constraint to prevent that happening? — Dai
– Dai, Commented Oct 13, 2020 at 8:46
@Dai How would you identify connection between two rows. As I understood his case, he wants to join rows where additional columns (PersonalLoan and HomeLoan) are filled based on LoanType — Solo Omsarashvili
– Solo Omsarashvili, Commented Oct 13, 2020 at 8:54
@SoloOmsarashvili See my answer, which does not use a JOIN, it uses a GROUP BY. — Dai
– Dai, Commented Oct 13, 2020 at 8:55

IVO GELOV · Accepted Answer · 2020-10-13 08:55:18Z

3

You can self-join the table so that you will be able to combine the 2 kinds of loans into single row:

SELECT t1.id, t1.name, t1.amount AS personal, t2.amount AS home
FROM customer AS t1
LEFT JOIN customer AS t2 ON t1.name = t2.name AND t2.loantype = 2
WHERE t1.loantype = 1

If you are looking for just 1 user - you can speedup the query by limiting the size of the JOIN:

SELECT t1.id, t1.name, t1.amount AS personal, t2.amount AS home
FROM (SELECT * FROM customer WHERE name = "ABC" AND loantype = 1) AS t1
LEFT JOIN customer AS t2 ON t1.name = t2.name AND t2.loantype = 2

edited Oct 13, 2020 at 8:55

answered Oct 13, 2020 at 8:50

IVO GELOV

14.3k1 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dai Over a year ago

Your query doesn't work if a customer has rows with loantype = 2 but doesn't have any rows with loantype = 1. It will also return home = NULL instead of home = 0 if a customer has a loantype = 1 but not any rows with loantype = 2 (though that may be desirable in this case).

IVO GELOV Over a year ago

@Dai You are right. But without enough information in the question - we can only guess about the needs or intentions of the OP.

Dai · Accepted Answer · 2020-10-13 09:10:56Z

Note that generally speaking you shouldn't denormalize data in SQL (e.g. converting rows to columns: SQL is row-oriented, not column-oriented) - I assume this is to simplify display logic - just be careful of using queries like this when you want to pass meaningful data to other parts of the database rather than directly to the user.
You need to GROUP BY first.
You can also simplify your CASE expressions: ELSE NULL is always implicit and if the CASE WHEN expression is an equality comparison then you can use the simpler switch-style syntax.
- So CASE WHEN a = b THEN c ELSE NULL END can be simplified to CASE a WHEN b THEN c END.
I've added COALESCE so the query will return 0 for the SUM aggregates if there are no matching rows instead of NULL. Note that COUNT (unlike SUM) generally doesn't need to be wrapped in a COALESCE (though I forget precisely how MySQL handles this - it also depends on what version of MySQL you're using and what strict-mode and ANSI/ISO-compliance options are enabled).

Note that the database design you posted seems to allow the same customer.name to have multiple loans of the same loantype.
- You can avoid this by adding a UNIQUE CONSTRAINT or UNIQUE INDEX or use a Composite Primary Key:
  - CREATE UNIQUE INDEX UX_name_loantype ON customer ( name, loantype )
- To prevent those rows from causing issues in this query, this uses a SUM and a COUNT to make it clear to readers that the data is an aggregate over multiple rows:

SELECT
    name,
    
    COUNT( CASE Loantype WHEN 1 THEN 1 END ) AS CountPersonalLoans,
    COALESCE( SUM( CASE Loantype WHEN 1 THEN Amount END ), 0 ) AS SumPersonalLoans,

    COUNT( CASE Loantype WHEN 2 THEN 1 END ) AS CountHomeLoans,
    COALESCE( SUM( CASE Loantype WHEN 2 THEN Amount END ), 0 ) AS SumHomeLoans

FROM
    customer
GROUP BY
    name

To maximise query code reuse if you want to filter by name then convert this to a VIEW - or if it's a one-off then make it a CTE query, like so:

WITH aggs AS (
    SELECT
        name,
        
       COUNT( CASE Loantype WHEN 1 THEN 1 END ) AS CountPersonalLoans,
        COALESCE( SUM( CASE Loantype WHEN 1 THEN Amount END ), 0 ) AS SumPersonalLoans,

        COUNT( CASE Loantype WHEN 2 THEN 1 END ) AS CountHomeLoans,
        COALESCE( SUM( CASE Loantype WHEN 2 THEN Amount END ), 0 ) AS SumHomeLoans

    FROM
        customer
    GROUP BY
        name
)
SELECT
    name,
    CountPersonalLoans,
    SumPersonalLoans,
    CountHomeLoans,
    SumHomeLoans
FROM
    aggs
WHERE
    name = 'ABC'
ORDER BY
    name

Collectives™ on Stack Overflow

How can I get the multiple columns in a single row using MySQL?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related