CODEWITHSUNDEEP
phpmysql
Enoch
Enoch

Reputation: 1011

Dynamic Pivot MYSQL incomplete SQL

I have been having trouble with a project i've been working on for about 5 weeks now, i've made various stackoverflow posts along the way and i'm almost at the final hurdle.

I was having issues with duplicated data searching weekly sums but I seem to have figured that out but now my statement isn't completing. REf: Weekly Sum Dynamic Pivot MYSQL

Here is a fiddle with the data. http://sqlfiddle.com/#!9/a3610

$period = 'YEARWEEK';


    $sql = "
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'SUM(CASE WHEN (".$period."(date)) = ',
          (".$period."(date)),
          ' THEN AMOUNT else 0 END) AS `',
          (".$period."(date)),
          '`'
        )
      ORDER BY date ASC ) AS `pivot_columns`
    FROM record_offering
    WHERE date BETWEEN ? AND ?
    ORDER BY date ASC
";

$stmt = $pdo->prepare($sql);
$date_from = '2017-01-01';
$date_to   = '2017-10-01';
$stmt->execute([$date_from, $date_to]);
$row = $stmt->fetch();
$stmt->closeCursor();
$pivot_columns = $row['pivot_columns'];

$sql = "
    SELECT title AS `Service`, {$pivot_columns}
    from record_offering t1
    join setting_service ON t1.service_id = setting_service.id
    WHERE t1.date BETWEEN ? AND ?
    GROUP BY title asc WITH ROLLUP
";

$stmt = $pdo->prepare($sql);
$stmt->execute([$date_from, $date_to]);
$results = $stmt->fetchAll();
$stmt->closeCursor();

As you can see the last statement is incomplete: 

SELECT title AS `Service`, SUM(CASE WHEN (YEARWEEK(date)) = 201635 THEN AMOUNT else 0 END) AS `201635`,
SUM(CASE WHEN (YEARWEEK(date)) = 201703 THEN AMOUNT else 0 END) AS `201703`,
SUM(CASE WHEN (YEARWEEK(date)) = 201709 THEN AMOUNT else 0 END) AS `201709`,
SUM(CASE WHEN (YEARWEEK(date)) = 201713 THEN AMOUNT else 0 END) AS `201713`,
SUM(CASE WHEN (YEARWEEK(date)) = 201715 THEN AMOUNT else 0 END) AS `201715`,
SUM(CASE WHEN (YEARWEEK(date)) = 201717 THEN AMOUNT else 0 END) AS `201717`,
SUM(CASE WHEN (YEARWEEK(date)) = 201718 THEN AMOUNT else 0 END) AS `201718`,
SUM(CASE WHEN (YEARWEEK(date)) = 201722 THEN AMOUNT else 0 END) AS `201722`,
SUM(CASE WHEN (YEARWEEK(date)) = 201723 THEN AMOUNT else 0 END) AS `201723`,
SUM(CASE WHEN (YEARWEEK(date)) = 201725 THEN AMOUNT else 0 END) AS `201725`,
SUM(CASE WHEN (YEARWEEK(date)) = 201726 THEN AMOUNT else 0 END) AS `201726`,
SUM(CASE WHEN (YEARWEEK(date)) = 201735 THEN AMOUNT else 0 END) AS `201735`,
SUM(CASE WHEN (YEARWEEK(date)) = 201736 THEN AMOUNT else 0 END) AS `201736`,
SUM(CASE WHEN (YEARWEEK(date)) = 201
from record_offering t1
join setting_service ON t1.service_id = setting_service.id
WHERE t1.`date` BETWEEN ? AND ?
GROUP BY title asc WITH ROLLUP

I have tried escaping the query in various ways but either the query completes and my data is duplicated or it doesn't compile at all.

Upvotes: 0

Views: 96

Answers (2)

Strawberry
Strawberry

Reputation: 33945

To my way of thinking, this (or something very like it) is ALL the sql you need for this problem. Everything else can, and should, be handled in the presentation layer. 

SELECT YEARWEEK(x.date) yw
     , x.title
     , COALESCE(SUM(y.amount),0) total
  FROM setting_service x
  LEFT
  JOIN record_offering y
    ON y.service_id = x.id
 GROUP 
    BY yw
     , x.id;

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

GROUP_CONCAT has a limit off 1024 bytes.

Use

SET SESSION group_concat_max_len = @@max_allowed_packet

Before the GROUP_CONCAT query.

Upvotes: 2

Related Questions