0

I am using MariaDB 10.2.

The Customer Tag Table looks as below

table1

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

table2

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');
Rick James
  • 135,179
  • 13
  • 127
  • 222
Adarsh Madrecha
  • 6,364
  • 11
  • 69
  • 117

1 Answers1

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
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