Dynamic pivot table in MySQL

Question

I'm trying to create a pivot table based on 200+ different values. A friend has prepared a working example of what I need in T-SQL but the ROW_NUMBER function isn't available in MySQL and I have been unable to translate this to do what I need.

The UniqueTally value is a count of how many distinct values for Word have appeared before this one. The example below shows that when "a" appears again in row 3, this doesn't increase the UniqueTally as it was also in row 1.

My data looks like this:

Table "Word"

| WordID     | MemberID   | Word     | UniqueTally   
| 1          | 1          | a        | 1
| 2          | 1          | b        | 2
| 3          | 1          | a        | 2
| 4          | 2          | c        | 1
| 5          | 2          | d        | 2
| 6          | 2          | e        | 3

I have 200+ Members and I'm trying to create a table that shows how the Unique count increases across all members. I.e. Aligning the UniqueTally value by Member. Word index is the row entry number for this member

| WordIndex  | UniqueTallyMember1 | UniqueTallyMember2 | etc   
| 1          | 1                  | 1        
| 2          | 2                  | 2        
| 3          | 2                  | 3        

A friend kindly generated the code I need in T-SQL :

/* Return the data */
SELECT
   [WordSpoken]
   ,[182] AS [Mem182_UniqueTally]
   ,[200] AS [Mem200_UniqueTally]
FROM (
   SELECT [WordSpoken], [MemberID], [UniqueTally]
   FROM (
          SELECT
                 ROW_NUMBER() OVER(PARTITION BY [MemberID] ORDER BY 
[WordID]) AS [WordSpoken]
                 ,[MemberID]
                 ,[UniqueTally]
          FROM [dbo].[ut_test]
          ) AS [test]
   ) AS [tbl]
PIVOT (MAX(UniqueTally) FOR MemberID IN ([182],[200])) AS [pvt]
GO

This uses only 2 MemberID values but it works in as much as aligning the Unique values. I need something similar in MySQL that also takes in the MemberID dynamically. Is this possible?

I've tried Googling and searching other similar posts about this but I'm unable to translate these directly to my problem.

Answer 1

A direct way to simulate the ROW_NUMBER is by using incrementing a session variable conditionally using ORDER BY. See this example:

#DROP TEMPORARY TABLE IF EXISTS sessionVarDemo;
CREATE TABLE sessionVarDemo(memberId INT UNSIGNED);

INSERT INTO sessionVarDemo VALUES (1), (2), (3), (2), (3), (1), (2), (2), (1), (1);

SET @VMemberID := 0, @VCounter := 0;

#Ensure data is correctly ordered
#DROP TEMPORARY TABLE IF EXISTS sessionVarDemoOrdered;
CREATE TEMPORARY TABLE sessionVarDemoOrdered SELECT memberId FROM sessionVarDemo ORDER BY memberId;

SELECT @VCounter := IF(@VMemberID != memberId, 0, @VCounter + 1) rowNumber, @VMemberID := memberId memberId FROM sessionVarDemoOrdered A;

Note that this may be possible without the temp table depending on your local setup with something like this:

SELECT @VCounter := IF(@VMemberID != memberId, 0, @VCounter + 1) rowNumber, @VMemberID := memberId memberId FROM sessionVarDemo ORDER BY memberId A;

Regards,

Edit 1

If your run this code, do you get the following output with the memberid from 1-3 and the rownumber incrementing correctly?

#DROP TEMPORARY TABLE IF EXISTS sessionVarDemo;
CREATE TABLE sessionVarDemo(memberId INT UNSIGNED);

INSERT INTO sessionVarDemo VALUES (1), (2), (3), (2), (3), (1), (2), (2), (1), (1);

SET @VMemberID := 0, @VCounter := 0;

SELECT @VCounter := IF(@VMemberID != memberId, 0, @VCounter + 1) rowNumber, @VMemberID := memberId memberId FROM sessionVarDemo ORDER BY memberId A;

+-----------+----------+
| rowNumber | memberId |
+-----------+----------+
|         0 |        1 |
|         1 |        1 |
|         2 |        1 |
|         3 |        1 |
|         4 |        1 |
|         5 |        1 |
|         6 |        1 |
|         7 |        1 |
|         0 |        2 |
|         1 |        2 |
|         2 |        2 |
|         3 |        2 |
|         4 |        2 |
|         5 |        2 |
|         6 |        2 |
|         7 |        2 |
|         0 |        3 |
|         1 |        3 |
|         2 |        3 |
|         3 |        3 |
+-----------+----------+
20 rows in set (0.00 sec)

Edit 2

Here's how I'd implement the ROW_NUMBER in your friends pseudocode using session variables:

SET @VMemberID := 0, @VCounter := 0;

/* Return the data */
SELECT
   [WordSpoken]
   ,[182] AS [Mem182_UniqueTally]
   ,[200] AS [Mem200_UniqueTally]
FROM (
   SELECT [WordSpoken], [MemberID], [UniqueTally]
   FROM (
          SELECT @VCounter := IF(@VMemberID != memberId, 0, @VCounter + 1) rowNumber, @VMemberID := memberId memberId
          FROM [dbo].[ut_test]
          ORDER BY [WordSpoken]
                 ,[MemberID]
                 ,[UniqueTally]
          ) AS [test]
   ) AS [tbl]
PIVOT (MAX(UniqueTally) FOR MemberID IN ([182],[200])) AS [pvt]
GO

James