CODEWITHSUNDEEP
mysql
Gnik
Gnik

Reputation: 7426

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

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

Upvotes: 1

Views: 1164

Answers (2)

Dai
Dai

Reputation: 155035

  • 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

Upvotes: 1

IVO GELOV
IVO GELOV

Reputation: 14259

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

Upvotes: 3

Related Questions