SQL Dynamic Columns

Question

I have the below SQL statement. The column wrapupcode can be up to four values:

Sales, Service, Meeting, Other

When the SQL is run, the column wrapupcode is showing as a single column. What I would like to work out, is to dynamically create a new wrapupcode based on the value. We may added or remove the codes, otherwise I would case for the value.

This is based on a MySQL database.

Data currently looks like:

user  wrapupcode  intialtime  endofwrapup  timediff
joe   Service     11:38       11:39        1
jenny Sales       11:35       11:36        1
joe   Service     11:41       11:42        1

But would like to see if its possible via SQL on a MySQL database:

user  Service  Sales     timediff
joe   2                  2
jenny          1         1

I can do the sum/avg for the times and totals, its just adding a new column on each different wrapupcode.

SELECT
     ( SELECT loginid FROM `axpuser` WHERE age.userid = axpuser.pkey ) as user,
     ( SELECT name FROM `breakcode` WHERE age.wrapupcode = pkey ) as wrapupcode,
     time(age.`instime`) as initialtime,
     age.`ENDOFWRAPUPTIME` AS endofwrapup,
     timediff(age.`ENDOFWRAPUPTIME`, time(age.`instime`)) as timediff
  FROM
     agentcallinformation age
  WHERE
     age.endofwrapuptime IS NOT null and ( SELECT name FROM `breakcode` WHERE age.wrapupcode =   pkey ) <> ''

When you say "create a new wrapupcode based on the value" what value are you talking about. Could you post some sample data and the expected output? — GarethD
– GarethD, Commented Oct 31, 2012 at 14:13
possible duplicate of MySQL pivot row into dynamic number of columns — RichardTheKiwi
– RichardTheKiwi, Commented May 3, 2013 at 9:02

Taryn · Accepted Answer · 2012-10-31 16:18:08Z

The basic syntax will be:

select user,
    sum(case when wrapupcode = 'Service' then 1 else 0 end) Service,
    sum(case when wrapupcode = 'Sales' then 1 else 0 end) Sales,
    sum(case when wrapupcode = 'Meeting' then 1 else 0 end) Meeting,
    sum(case when wrapupcode = 'Other' then 1 else 0 end) Other,
    count(timediff) timediff
from
(       
    <yourquery>
) src
group by user

Hard-coded static version will be something similar to this:

select user,
    sum(case when wrapupcode = 'Service' then 1 else 0 end) Service,
    sum(case when wrapupcode = 'Sales' then 1 else 0 end) Sales,
    sum(case when wrapupcode = 'Meeting' then 1 else 0 end) Meeting,
    sum(case when wrapupcode = 'Other' then 1 else 0 end) Other,
    count(timediff) timediff
from
(       
    select u.loginid as user,
        b.name wrapupcode,
        time(age.`instime`) as initialtime,
        age.`ENDOFWRAPUPTIME` AS endofwrapup,
        count(timediff(age.`ENDOFWRAPUPTIME`,   time(age.`instime`))) as timediff
    from agentcallinformation age
    left join `axpuser` u
        on age.userid = u.pkey
    left join `breakcode` b
        on age.wrapupcode = b.pkey
        and age.wrapupcode <> ''
    WHERE age.endofwrapuptime IS NOT null 
) src
group by user

I changed the query to use JOIN syntax instead of the correlated subqueries.

If you need a dynamic version, then you can use prepared statements:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when wrapupcode = ''',
      name,
      ''' then 1 else 0 end) AS ',
      name
    )
  ) INTO @sql
FROM breakcode;

SET @sql = CONCAT('SELECT user, ', @sql, ' 
                    , count(timediff) timediff
                  from
                  (     
                    select u.loginid as user,
                        b.name wrapupcode,
                        time(age.`instime`) as initialtime,
                        age.`ENDOFWRAPUPTIME` AS endofwrapup,
                        count(timediff(age.`ENDOFWRAPUPTIME`,   time(age.`instime`))) as timediff
                    from agentcallinformation age
                    left join `axpuser` u
                        on age.userid = u.pkey
                    left join `breakcode` b
                        on age.wrapupcode = b.pkey
                        and age.wrapupcode <> ''
                    WHERE age.endofwrapuptime IS NOT null 
                ) src
                GROUP BY user');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Hi, looks like the prepared statement fails on the first SQL script.I have never used prepared statements before. I shall look into it.

Collectives™ on Stack Overflow

SQL Dynamic Columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related