Group concat Values separate with other rows as a name in sql query

Question

 SELECT GROUP_CONCAT(`flim_category_name` SEPARATOR '*') 
`flim_category_name`,country, GROUP_CONCAT(`flim_video` SEPARATOR '*') flim_video,
  GROUP_CONCAT(`flim_video_images` SEPARATOR '*') flim_video_images FROM 
  flim_management_table GROUP BY `country` limit 0,1

In this query I am getting the table structure like this

flim_category_name    country     flim_video                                       flim_video_images
Guru*Mersal*Aadi      India    ww.com,ww.com,ww.com* ww.com,ww.com,ww.com* ,,,     217953157.jpg,,,,,,,,,*,,,,,,,,,

I need to separator with start instead of separate row like this

flim1 flim2   flim3    country     flim_video1             flim_video2        flim_video3   images1        image2     image 3
Guru  Mersal   Aadi      India    ww.com,ww.com,ww.com  ww.com,ww.com,ww.com       ,,,    217953157.jpg,,,  ,,,,,     ,,,,,

I need to structure the table like this.

Mysql and Oracle DBs are different..which one are you using..? — Kaushik Nayak, Aug 27 '17 at 04:44
There is no answer to this. How can we know what you mean by "concatenate the column"? What column? — Brian Gottier, Aug 27 '17 at 04:50
you can achieve something near your result by mysql's group_concat — B.Mossavari, Aug 27 '17 at 04:54
[normalise the db](https://en.wikipedia.org/wiki/First_normal_form), this is terrible structure — , Aug 27 '17 at 05:03
I Corrected the question is there is they way to separate with columns in another row. — vallavan, Aug 28 '17 at 05:10

score 0 · Answer 1 · answered Aug 27 '17 at 05:37

In Oracle, the best I could come up with was this.

 WITH f AS
(
       SELECT 'Ghajini'             film_category_name ,
              'INDIA'               country ,
              'IMG1.jpg,iMAGE2.JPG' flim_images,
              'WW.COM,WW.COM,WW.COM' flim_url 
       FROM   dual
       UNION ALL
       SELECT 'dIWALE'              film_category_name ,
              'INDIA'               country ,
              'IMG1.jpg,iMAGE2.JPG' flim_images,
              'WW.COM,WW.COM'       flim_url
       FROM   dual
       UNION ALL
       SELECT 'gURU'                film_category_name ,
              'SOUTH AFRICA'        country,
              'IMG1.jpg,iMAGE2.JPG' flim_images,
              'WW.COM,WW.COM'       flim_url
       FROM   dual ), f2 AS
(
         SELECT   f.* ,
                  Row_number() over (PARTITION BY country ORDER BY ROWNUM ) rn
         FROM     f ), f3 AS
(
         SELECT   Listagg(film_category_name,',') within GROUP ( ORDER BY ROWNUM ) over ( PARTITION BY country ) film_category_name ,
                  country ,
                  flim_images,
                  flim_url,
                  rn
         FROM     f2 )
SELECT *
FROM   f3 pivot ( max(flim_images) AS flim_images_row  ,
                  max(flim_url)    AS flim_url FOR rn IN (1,2) ) p
ORDER BY film_category_name;

Output is something like this:

FILM_CATEGORY_NAME    COUNTRY           1_FLIM_IMAGES_ROW    1_FLIM_URL             2_FLIM_IMAGES_ROW   2_FLIM_URL
Ghajini,dIWALE         INDIA            IMG1.jpg,iMAGE2.JPG  WW.COM,WW.COM,WW.COM   IMG1.jpg,iMAGE2.JPG WW.COM,WW.COM
gURU                   SOUTH AFRICA     IMG1.jpg,iMAGE2.JPG  WW.COM,WW.COM   -

If you do not know the number of rows you will have for each country , then your only option is to generate and execute the query dynamically using dynamic SQL.I do not however recommend it because the structure and design of your table is inefficient for any data extraction. You need to restructure your components and create separate tables wherever required to store and retrieve useful data.

score -2 · Answer 2 · answered Aug 27 '17 at 04:59

You can use group_concat function of Mysql:

select group_concat(Film_category_name), Country, group_concat(flim_images),group_concat(flim_url) from table group by country

and for oracle check this:
Is there any function in oracle similar to group_concat in mysql?

Group concat Values separate with other rows as a name in sql query

2 Answers2