How to use the CONCAT() function in MySQL?

Question

I have two tables in a one to many relationship. In table 2, I have three columns I am looking to transform into row data. Why? these will be used to merge with another process (as a data source) for producing documents. Please see the attachment for the breakdown and the related output.

Here is the first successful query having the accurate information needed.

SELECT 
tblClients.fldDRCClientID,
fldLastName,
e1.fldCreditorName,
e1.fldDebtAccountNumber,
e1.fldEnrolledDebt
From tblClients
Left Join tblDebtAccountNumber as e1 ON e1.tblClients_fldDRCClientID = tblClients.fldDRCClientID;

First query results:

first query results

did you look into sql join tables ? - you are not concatenating text .. is it ? you want a join display of fields — zod, Aug 07 '17 at 20:36
Welcome to Stack Overflow. Please don't hide the most important part of your question in a hyperlinked image. Instead, [edit] your question, paste in the text of your queries and examples, and indent them by four spaces to indicate they are code. For what it's worth, **pivoting** is the general term for your problem, and it is a notorious pain in the neck in MySQL. — O. Jones, Aug 07 '17 at 20:41
Actually, you want to transform into column data from row data; not the otherway. This type of situation is best handled in the Presentation layer, not SQL. If you must do it in the DB, then you need a dynamic pivot which mySQL doesn't support, so you'd have to use dynamic case statements and simulate row_number(). Bluefeet has a good example to follow: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — xQbert, Aug 07 '17 at 20:53
The suggested final layout is a bad idea for a data source to any other process. Much better to retain a normalized set of rows. Consider the possibility of a single client having many more accounts (perhaps hundreds of them), the "width" of such a record will be truly horrible to program for. Just join the 2 tables and accept that some columns will repeat (e.g. the client id) ; that isn't bad, it is predictable and simple to program for. — Paul Maxwell, Aug 08 '17 at 02:02
I hear ya. However, the average client will have only a handful (more than 8 would be unusual). The second consideration is the records have three components that have to remain together; A-account number, B- balance, and C-creditor name. So it has to be able to be delivered in a matrix format onto the document. A, B, C line 1; D, E, F line 2 and so on — Dak, Aug 09 '17 at 00:16
AS @xQbert has told, it is doable, but without table structure and some example data like e.g. in a http://sqlfiddle.com/ or posted in your edit, we cannot help adapting the solution from e.g. Bluefeet. — Myonara, Aug 09 '17 at 00:21
The suggested layout remains a bad idea no matter how you rationalize it. Even if "more than 8 would be unusual" *it is not impossible*. Keep the row width narrow, avoid the pivot. Truly it does not matter if there is some repetition in the data source; every row can repeat A-account number, B- balance, and C-creditor name if you need it to. — Paul Maxwell, Aug 09 '17 at 05:12
By the way, an "image of data" isn't sample data, it is just an image. Add data - for each table - as parsable text into your question so that both tables can be populated and so that the overall query can operate. (or create a http://sqlfiddle.com instance) — Paul Maxwell, Aug 09 '17 at 05:17
here is the schema and two queries. The first query shows all the accurate data http://sqlfiddle.com/#!9/12d241/2/0 and the second query follows the bluefeet example http://sqlfiddle.com/#!9/12d241/3 However, the end result as you can see is not accurate. It is grouping and aligning account numbers with incorrect enrolled debt and not transforming the records into their respective positions. — Dak, Aug 11 '17 at 16:53
Note: one of the issues appears to be the derived table. The select @row statement appears to run ok until the ) d occurs. The data integrity still holds if the "d" and the GROUP BY d.IndividualNumber is commented out. See here: — Dak, Aug 11 '17 at 18:42

score 0 · Answer 1 · answered Oct 01 '19 at 17:34

(Posted on behalf of the question author to move it from the comments).

Here is my query:

SELECT idt.fldDRCClientID
    , idt.fldLastName
    , COALESCE(grp.fldCreditorName1, '') as fldCreditorName1
    , COALESCE(grp.fldDebtAccountNumber1, '') as fldDebtAccountNumber1
    , COALESCE(grp.fldEnrolledDebt1, '') as fldEnrolledDebt1
    , COALESCE(grp.fldCreditorName2, '') as fldCreditorName2
    , COALESCE(grp.fldDebtAccountNumber2, '') as fldDebtAccountNumber2
    , COALESCE(grp.fldEnrolledDebt2, '') as fldEnrolledDebt2
    , COALESCE(grp.fldCreditorName3, '') as fldCreditorName3
    , COALESCE(grp.fldDebtAccountNumber3, '') as fldDebtAccountNumber3
    , COALESCE(grp.fldEnrolledDebt3, '') as fldEnrolledDebt3
    , COALESCE(grp.fldCreditorName4, '') as fldCreditorName4
    , COALESCE(grp.fldDebtAccountNumber4, '') as fldDebtAccountNumber4
    , COALESCE(grp.fldEnrolledDebt4, '') as fldEnrolledDebt4
    , COALESCE(grp.fldCreditorName5, '') as fldCreditorName5
    , COALESCE(grp.fldDebtAccountNumber5, '') as fldDebtAccountNumber5
    , COALESCE(grp.fldEnrolledDebt5, '') as fldEnrolledDebt5
    , COALESCE(grp.fldCreditorName6, '') as fldCreditorName6
    , COALESCE(grp.fldDebtAccountNumber6, '') as fldDebtAccountNumber6
    , COALESCE(grp.fldEnrolledDebt6, '') as fldEnrolledDebt6
FROM tblClients idt
LEFT JOIN (
    SELECT d.IndividualNumber as IndividualNumber
        , MAX(CASE WHEN row = 1 THEN d.fldCreditorName END) AS fldCreditorName1
        , MAX(CASE WHEN row = 1 THEN d.fldDebtAccountNumber END) AS fldDebtAccountNumber1
        , MAX(CASE WHEN row = 1 THEN d.fldEnrolledDebt END) AS fldEnrolledDebt1
        , MAX(CASE WHEN row = 2 THEN d.fldCreditorName END) AS fldCreditorName2
        , MAX(CASE WHEN row = 2 THEN d.fldDebtAccountNumber END) AS fldDebtAccountNumber2
        , MAX(CASE WHEN row = 2 THEN d.fldEnrolledDebt END) AS fldEnrolledDebt2
        , MAX(CASE WHEN row = 3 THEN d.fldCreditorName END) AS fldCreditorName3
        , MAX(CASE WHEN row = 3 THEN d.fldDebtAccountNumber END) AS fldDebtAccountNumber3
        , MAX(CASE WHEN row = 3 THEN d.fldEnrolledDebt END) AS fldEnrolledDebt3
        , MAX(CASE WHEN row = 4 THEN d.fldCreditorName END) AS fldCreditorName4
        , MAX(CASE WHEN row = 4 THEN d.fldDebtAccountNumber END) AS fldDebtAccountNumber4
        , MAX(CASE WHEN row = 4 THEN d.fldEnrolledDebt END) AS fldEnrolledDebt4
        , MAX(CASE WHEN row = 5 THEN d.fldCreditorName END) AS fldCreditorName5
        , MAX(CASE WHEN row = 5 THEN d.fldDebtAccountNumber END) AS fldDebtAccountNumber5
        , MAX(CASE WHEN row = 5 THEN d.fldEnrolledDebt END) AS fldEnrolledDebt5
        , MAX(CASE WHEN row = 6 THEN d.fldCreditorName END) AS fldCreditorName6
        , MAX(CASE WHEN row = 6 THEN d.fldDebtAccountNumber END) AS fldDebtAccountNumber6
        , MAX(CASE WHEN row = 6 THEN d.fldEnrolledDebt END) AS fldEnrolledDebt6
    FROM
    (
      SELECT @row := CASE WHEN tblClients_fldDRCClientID = @id 
          THEN @row + 1 ELSE 1 END as row 
        , @id := tblClients_fldDRCClientID as IndividualNumber
          , inf.fldCreditorName
          , inf.fldDebtAccountNumber
          , inf.fldEnrolledDebt
      FROM (SELECT @row := 0, @id := 0) v
        , tblDebtAccountNumber as inf 
      ORDER BY inf.tblClients_fldDRCClientID
    ) d 
    GROUP BY d.IndividualNumber
) grp
    ON grp.IndividualNumber = idt.fldDRCClientID
;

SQL Fiddle

How to use the CONCAT() function in MySQL?

1 Answers1