single record with comma separated value

Question

I am using the sql query like

SELECT fun_systemuser(UserID) as ConsultantName,
       fun_store(UserID) as StoreName,
       count(UserID) as No_of_PhoneModel,
       PhoneModelID,
       (select LeegraRegion 
          from store 
         where ID=claim.StoreID) as Region,
       fun_network(UserID) as ChannelName,
       fun_group(claim.StoreID) as GroupName,
       fun_campaigns(UserID) as CampaignName  
  FROM `claim` 
 where UserID IN (SELECT id 
                    FROM `systemuser` 
                   WHERE `RoleID`=1 
                     and `Topconsultant`=1) 
   and StatusID=5 
 group by UserID ,PhoneModelID

I have query result like given below:

enter image description here

There are 4 records for single consultant with different phone model id and no. of sales of phone model id.

I want to single record for every consultant name and there should be show phone model and no.of phone model with comma separated value.

Ex. single record should display like below

Thomson 4U-Sunnypark 3,2,3,1  1,3,6,9 Pretoria Vodacom Sachar Mobile vodacom Campaigns

Is anybody know what should I use in query so I can get the result as I want. Thanks in advance.

Refer: http://stackoverflow.com/questions/15742716/how-to-get-column-values-in-one-comma-separated-value — ngrashia, May 30 '14 at 12:21
Use string concatenation to concatenate the fields, e.g. `||` or `CONCAT`. — davidhigh, May 30 '14 at 12:23

Oscar Pérez · Answer 1 · 2014-05-31T12:48:56.697

0

You must use GROUP_CONCAT. For example:

  SELECT ConsultantName,
         StoreName,
         GROUP_CONCAT(DISTINCT No_of_Phonemodel SEPARATOR ',') ,
         GROUP_CONCAT(DISTINCT PhoneModelID SEPARATOR ',') ,
         Region
    FROM (
           SELECT fun_systemuser(UserID) as ConsultantName,
                  fun_store(UserID) as StoreName,
                  count(UserID) as No_of_PhoneModel,
                  PhoneModelID,
                  (select LeegraRegion 
                     from store 
                    where ID=claim.StoreID) as Region,
                  fun_network(UserID) as ChannelName,
                  fun_group(claim.StoreID) as GroupName,
                  fun_campaigns(UserID) as CampaignName  
             FROM `claim` 
            where UserID IN (SELECT id 
                               FROM `systemuser` 
                              WHERE `RoleID`=1 
                                and `Topconsultant`=1) 
              and StatusID=5 
            group by UserID ,PhoneModelID
         ) as S
GROUP BY ConsultantName,
         StoreName,
         Region

edited May 31 '14 at 12:48

answered May 30 '14 at 12:31

Oscar Pérez

4,377
1
17
36

3

You forgot to add the `GROUP BY` clause – Pred May 30 '14 at 12:33
Oscar Pérez i can not use the GROUP_CONCAT for count(...) value. I have updated my question and put the actual query now can you please help me? – user1181940 May 30 '14 at 12:42
I'm not 100% sure, but I guess you could use my query changing the `table` with your whole query... If you can create an sqlfiddle with your data, we could try.... – Oscar Pérez May 30 '14 at 12:50
Thanks for your help but i have still issue with getting comma separate value for count value.When i use the Group contact with those value then it gives error like "#1111 - Invalid use of group function". I am using like GROUP_CONCAT(count( PhoneModelID) SEPARATOR ',') then it will give error. – user1181940 May 30 '14 at 13:16
I edited the answer. Could you please try it? – Oscar Pérez May 31 '14 at 12:49

single record with comma separated value

1 Answers1