CODEWITHSUNDEEP
mysqlsql
The50
The50

Reputation: 1166

SQL Get multiple values into one column separated by comma

Need some help on this SQL Query. Node is a table of products and each product has n images on table field_data_field_images. I need to select all of those image file names into one column separated by comma (as example "image1.jpg, image2.jpg" and so on). So first I need to get all of the image ID's from field_data_field_images and then get the file names itself from file_managed. 

SELECT node.*,
file_managed.filename,
field_data_field_product_code.field_product_code_value,
FROM node

LEFT JOIN field_data_field_images ON node.nid = field_data_field_images.entity_id
LEFT JOIN file_managed ON field_data_field_images.field_images_fid = file_managed.fid

WHERE node.language = 'lt' AND node.type = 'products'
GROUP BY nid

Upvotes: 1

Views: 10748

Answers (2)

Sameer
Sameer

Reputation: 381

IF MS-SQL follow below code:

 DECLARE @TableVar TABLE
 (
  nid INT ,
  images NVARCHAR(400)
 );
 INSERT INTO @TableVar
    ( nid ,
      images
    )
    SELECT nid,images

FROM node

LEFT JOIN field_data_field_images 
ON node.nid =     field_data_field_images.entity_id
LEFT JOIN file_managed 
ON field_data_field_images.field_images_fid =   file_managed.fid

WHERE node.language = 'lt' AND node.type = 'products'
GROUP BY nid

SELECT DISTINCT
    nid ,
    STUFF(( SELECT  ',' + images
            FROM    @TableVar AS T
            WHERE   T.nid = T2.nid
          FOR
            XML PATH('')
          ), 1, 1, '') AS images
 FROM   @TableVar T2
 GROUP BY nid ,
    images

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use group_concat(). The following query also introduces table aliases so the query is easier to write and to read:

SELECT n.*,
       GROUP_CONCAT(fm.filename) as filenames
FROM node n LEFT JOIN
     field_data_field_images dfi
     ON n.nid = dfi.entity_id LEFT JOIN
     file_managed fm
     ON dfi.field_images_fid = fm.fid
WHERE n.language = 'lt' AND n.type = 'products'
GROUP BY n.nid

Upvotes: 4

Related Questions