Mariadb move dynamic data from rows to columns with True / False

Question

I am using MariaDB 10.2.

The Customer Tag Table looks as below

I want to create a View/ Query which gives output as below

I was easily able to do so in Excel, but SQL query proved to be very difficult and erroneous.

I tried SQL

SELECT
  GROUP_CONCAT( t.tagname ) INTO @PivotQuery
FROM
  (SELECT
     clienttag.customer, tagname
   FROM
     clienttag
   GROUP BY
     clienttag.customer, clienttag.office) t;

SET @PivotQuery = CONCAT('SELECT @PivotQuery' FROM clienttag GROUP BY customer');

score 1 · Answer 1 · answered Jan 09 '18 at 08:13

1

Use conditional aggregation

SELECT customer, tagname,
      MAX(CASE WHEN tagName = 'A' THEN 1 END) as A,
      MAX(CASE WHEN tagName = 'B' THEN 1 END) as B,
      MAX(CASE WHEN tagName = 'C' THEN 1 END) as C,
      MAX(CASE WHEN tagName = 'D' THEN 1 END) as D,
      MAX(CASE WHEN tagName = 'E' THEN 1 END) as E
FROM clienttag
GROUP BY customer, tagname

answered Jan 09 '18 at 08:13

Radim Bača

10,646
1
19
33

A, B, C these are dynamic fields. Actually, these values are something like this - `green, blue, white, car, jeep, birthday` – Adarsh Madrecha Jan 09 '18 at 09:26
@AdarshMadrecha ok, I understand. You may compile the query using the dynamic SQL: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns/12005676#12005676 – Radim Bača Jan 09 '18 at 09:30

Mariadb move dynamic data from rows to columns with True / False

1 Answers1