Posted By

ktrout on 01/10/16


Tagged

mysql dynamic pivot


Versions (?)

mysql pivot data


 / Published in: SQL
 

pivot data. input source query in the form :

+--------+-----------+-------------+-------------+
| name   | pivot_fid | pivot_fname | pivot_value |
+--------+-----------+-------------+-------------+
| apple  |         1 | is red      |           1 |
| apple  |         2 | is blue     |           0 |
| orange |         1 | is red      |           1 |
| orange |         2 | is blue     |           0 |
+--------+-----------+-------------+-------------+

and fields to pivot around ( eg. name ), outputs :

+--------+--------+---------+
| name   | is red | is blue |
+--------+--------+---------+
| apple  |      1 |       0 |
| orange |      1 |       0 |
+--------+--------+---------+
  1. DROP procedure IF EXISTS pivot_data;
  2. delimiter $$
  3. CREATE procedure pivot_data
  4. ( IN pivot_source varchar(1024),
  5. IN pivot_basefields varchar(81))
  6. begin
  7. SET @pivot_sql :=
  8. 'select #pivot_basefields#,
  9. #pivot_aliases#
  10. from
  11. (
  12. select #first_user_dets#,
  13. #pivot_fields#
  14. from
  15. ( #pivot_source# ) #first_alias#
  16. inner join
  17. #all_joins#
  18. ) q
  19. order by #pivot_basefields#
  20. ;';
  21.  
  22. SET @pivot_sql := REPLACE(@pivot_sql, '#pivot_source#', pivot_source);
  23. SET @pivot_sql := REPLACE(@pivot_sql, '#pivot_basefields#', pivot_basefields);
  24.  
  25. SELECT group_concat('#last_alias#.', fieldname, ' = #alias#.', fieldname SEPARATOR ' and ') INTO @base_join_condition
  26. FROM
  27. (
  28. SELECT seq.n, trim(substring_index(substring_index(t.pivot_basefields, ',', seq.n), ',', -1)) AS fieldname
  29. FROM ( SELECT pivot_basefields ) t
  30. CROSS JOIN
  31. (
  32. SELECT a1.n * 10 + a0.n + 1 n
  33. FROM digits_v a1
  34. CROSS JOIN digits_v a0
  35. ORDER BY n
  36. ) seq
  37. WHERE seq.n <= 1 + (length(t.pivot_basefields) - length(REPLACE(t.pivot_basefields, ',', '')))
  38. ) split
  39. ;
  40.  
  41. SET @pivot_block :=
  42. '( #pivot_source# ) #alias#
  43. on 1 = 1
  44. and #base_join_condition#
  45. and #last_alias#.pivot_fid = #alias#.pivot_fid - 1
  46. inner join #all_joins#';
  47.  
  48. SET @pivot_block := REPLACE(@pivot_block, '#pivot_source#', pivot_source);
  49. SET @pivot_block := REPLACE(@pivot_block, '#base_join_condition#', @base_join_condition);
  50.  
  51. SET @qry_write_joins := REPLACE('select count(*) into @ignore
  52. from
  53. (
  54. select
  55. @pivot_sql := replace(@pivot_sql, ''#all_joins#'', replace(replace(@pivot_block, ''#alias#'', concat(''pivot'', right_id)), ''#last_alias#'', concat(''pivot'', left_id)))
  56. from
  57. (
  58. select `left`.pivot_fid as left_id, min(`right`.pivot_fid) as right_id
  59. from ( #pivot_source# ) `left`
  60. inner join ( #pivot_source# ) `right`
  61. on `left`.pivot_fid = `right`.pivot_fid - 1
  62. group by 1
  63. ) t
  64. order by left_id
  65. ) `ignore`;', '#pivot_source#', pivot_source);
  66. prepare write_joins FROM @qry_write_joins;
  67. EXECUTE write_joins;
  68. deallocate prepare write_joins;
  69.  
  70. SET @qry_get_first_alias := REPLACE('select concat(''pivot'', pivot_fid) into @first_alias
  71. from ( #pivot_source# ) p
  72. order by pivot_fid
  73. limit 1;', '#pivot_source#', pivot_source);
  74. prepare get_first_alias FROM @qry_get_first_alias;
  75. EXECUTE get_first_alias;
  76. deallocate prepare get_first_alias;
  77.  
  78. SELECT group_concat(@first_alias, '.', fieldname SEPARATOR ',') INTO @first_user_dets
  79. FROM
  80. (
  81. SELECT seq.n, trim(substring_index(substring_index(t.pivot_basefields, ',', seq.n), ',', -1)) AS fieldname
  82. FROM ( SELECT pivot_basefields ) t
  83. CROSS JOIN
  84. (
  85. SELECT a1.n * 10 + a0.n + 1 n
  86. FROM digits_v a1
  87. CROSS JOIN digits_v a0
  88. ORDER BY n
  89. ) seq
  90. WHERE seq.n <= 1 + (length(t.pivot_basefields) - length(REPLACE(t.pivot_basefields, ',', '')))
  91. ) split
  92. ;
  93.  
  94. -- select concat(@first_alias, '.user_id,',@first_alias,'.username') into @first_user_dets;
  95.  
  96. SET @qry_get_pivot_fields := REPLACE('select group_concat(''pivot'', pivot_fid, ''.pivot_value `'', pivot_fname, ''`'' SEPARATOR '','') into @pivot_fields
  97. from (
  98. select distinct pivot_fid, pivot_fname
  99. from ( #pivot_source# ) q ) p;', '#pivot_source#', pivot_source);
  100. prepare get_pivot_fields FROM @qry_get_pivot_fields;
  101. EXECUTE get_pivot_fields;
  102. deallocate prepare get_pivot_fields;
  103.  
  104. SET @qry_get_pivot_aliases = REPLACE('select group_concat(''`'', pivot_fname, ''`'' order by pivot_fid SEPARATOR '','') into @pivot_aliases
  105. from (
  106. select distinct pivot_fid, pivot_fname
  107. from ( #pivot_source# ) q ) p;', '#pivot_source#', pivot_source);
  108. prepare get_pivot_aliases FROM @qry_get_pivot_aliases;
  109. EXECUTE get_pivot_aliases;
  110. deallocate prepare get_pivot_aliases;
  111.  
  112. SELECT count(*) INTO @IGNORE
  113. FROM
  114. (
  115. SELECT
  116. @pivot_sql := REPLACE(@pivot_sql, '#first_user_dets#', @first_user_dets),
  117. @pivot_sql := REPLACE(@pivot_sql, '#pivot_fields#', @pivot_fields),
  118. @pivot_sql := REPLACE(@pivot_sql, '#pivot_aliases#', @pivot_aliases),
  119. @pivot_sql := REPLACE(@pivot_sql, '#first_alias#', @first_alias),
  120. @pivot_sql := REPLACE(@pivot_sql, 'inner join #all_joins#', '')
  121. ) `ignore`
  122. ;
  123.  
  124. -- select @pivot_sql;
  125.  
  126. prepare pivot_sql FROM @pivot_sql;
  127. EXECUTE pivot_sql;
  128. deallocate prepare pivot_sql;
  129.  
  130. end $$
  131. delimiter ;

Report this snippet  

You need to login to post a comment.