Bug #72589 No relevant pivot table/crosstab sql query unlike Microsoft
Submitted: 9 May 2014 6:31 Modified: 9 May 2014 6:40
Reporter: Rommel Sanchez Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.6.17 OS:Microsoft Windows (XP, 7 , 8)
Assigned to: CPU Architecture:Any

[9 May 2014 6:31] Rommel Sanchez
Description:
Why MySQL dont have sql syntax for pivoting table / crosstab query.  The sql syntax of Microsoft SQL that using TRANSFORM and PIVOT keyword is very useful and friendly.

With MS SQL pivot sample code:

TRANSFORM First(Table1.shiftid) AS FirstOfshiftid
SELECT Table1.ID, Table1.payid
FROM Table1
GROUP BY Table1.ID, Table1.payid
PIVOT Format([dtrdate],"Short Date");

I believe MySQL is capable of doing this. I hope this will be included on the next release of MySQL.

We love MySQL but if this lacks something like this, then is it really disappointing.

How to repeat:
Nothing to supply here as the code is not existing.

Suggested fix:
MySQL should have this pivot table/crosstab query because this facilitate ease of retrieving and formatting data.

Looking forward on this feature to be included on the next release of MySQL version.
[9 May 2014 6:40] Rommel Sanchez
Description:
Why MySQL dont have sql syntax for pivoting table / crosstab query.  The sql syntax of Microsoft SQL that using TRANSFORM and PIVOT keyword is very useful and friendly.

With MS SQL pivot sample code:

TRANSFORM First(Table1.shiftid) AS FirstOfshiftid
SELECT Table1.ID, Table1.payid
FROM Table1
GROUP BY Table1.ID, Table1.payid
PIVOT Format([dtrdate],"Short Date");

I believe MySQL is capable of doing this. I hope this will be included on the next release of MySQL.

We love MySQL but if this lacks something like this, then is it really disappointing.

How to repeat:
Nothing to supply here as the code is not existing.

Suggested fix:
MySQL should have this pivot table/crosstab query because this facilitate ease of retrieving and formatting data.

Looking forward on this feature to be included on the next release of MySQL version.
[24 Nov 2019 5:16] JJ Ward
Can someone on server development please comment on this?  Obviously UN/PIVOT is not standard SQL, but this is extremely useful for more than just tabulations - matrix transforms/joins are also applicable here.