Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Combining columns & rows

    Friends,

    I need to create a coma separated list of image file names from a table where each client has several records which in turn have several image file fields.

    The following query successfully groups all the file names into a coma separated result for each client.

    But now I need a single coma separated list of all the file names from the three fields. I can control which user_id(s) in a where statement.


    SELECT DISTINCT user_id, GROUP_CONCAT(', ', upl_1_cr, upl_2_cr, upl_3_cr) AS imageFiles
    FROM caseReport_n
    group by user_id
    ORDER BY user_id


    Thanks
    Nick

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't really understand what you're requesting, so consider this a "best guess" at what you actually want:
    Code:
    SELECT user_id, GROUP_CONCAT(', ', upl_cr) AS imageFiles
       FROM (SELECT DISTINCT user_id, upl_1_cr AS upl_cr
          FROM caseReport
          WHERE upl_1_cr IS NOT NULL
       UNION ALL SELECT user_id, upl_2_cr
          FROM caseReport
          WHERE upl_2_cr IS NOT NULL
       UNION ALL SELECT user_id, upl_3_cr
          FROM caseReport
          WHERE upl_3_cr IS NOT NULL) AS a 
       GROUP BY user_id
       ORDER BY user_id)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    Thanks, using union to get rid of NULLs hadn't occurred to me, I'll try this and get back to you.

    Nick

  4. #4
    Join Date
    Jan 2009
    Posts
    124
    Same result as my query.

    What I need as one row with all the file names, from all the rows, coma separated in one field.

    Three lines of results from your query as sample:
    line 1: NULL , 201106151154450004SMP.JPG,, ,, 10.30.08 hrs __[0001413].jpg,, ,, ,, L sag_1.jpg,, arad_1.jpg,, 201106151153330002SMP.JPG,, 10.30.08 hrs __[0001414].jpg,, ,, L transv_1.jpg,, rad_1.jpg

    line 2: 1 , savethedate2014.jpg,, ,,

    line3: 1044 , francis 3.pdf,, arroyo 2_1.pdf,, diez 1.pdf,, ,, vazquez 1.pdf,, franca 1.pdf,, ,, garcia 2_1.pdf,, coto 1.pdf,, franca 2.pdf,, francis 1.pdf,, gonzalez 1.pdf,, ,, davis 2.pdf,, ramirez 1.pdf,, gonzalez 2.pdf,, arroyo 1_2.pdf,, ,, diez 2.pdf,, franca 3.pdf,, vazquez 2.pdf,, garcia 1_1.pdf,, ,, coto 2 (2).pdf,, vazquez 3.pdf,, gonzalez 3.pdf,, francis 2.pdf,, davis 1.pdf,, coto 3.pdf,, ramirez 2.pdf

  5. #5
    Join Date
    Jan 2009
    Posts
    124

    some success but help still needed:)

    this query works partway:
    SELECT user_id, GROUP_CONCAT( upl_1_cr, ',',upl_2_cr, ',', upl_3_cr, ',',`upl_4_cr`, ',',report_1_cr, report_2_cr, ',', report_3_cr, ',',report_4_cr ) AS imageFiles
    FROM caseReport_n
    group by user_id
    ORDER BY user_id

    see attached table

    What I need now is to concatenate all the rows into a single row in a field called "AllimageFiles" ( ideally with no duplicate comas). So one long string of coma separated file names.

    Nick
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •