0

I'm creating a pivot table and I'd like the column names to be something like value1, value2, value3....

  CREATE TEMPORARY TABLE temp_table (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(255),
    date DATETIME ,
    person_id       INT ) ;

Here's the part where I'm naming the column. I tried ID, but with multiple personIDs, it makes the id going in random. I was trying the INTEGER AUTO_INCREMENT, but that throws an error

SET SESSION group_concat_max_len = 1000000 ;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(id = ''', c.id, ''', value, NULL)) AS "', **INTEGER auto_increment**, '"'))
INTO @column_sql1
FROM temp_table c ;

set @sql = concat("select person_id, ",  @column_sql1, " from temp_table group by `person_id`");
select @sql;
prepare stmt from @sql;
execute stmt;

Is there any way for each value column to be numbered, so later when I select I can choose the 1st, 2nd, and 3rd column instead of using a different field for the dynamic column name?
I've also tried row_number and many other things. Nothing seems to work.

Thanks for any help.

Error Message: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTeger auto_increment, '"')) INTO @column_sql1"

jared
  • 1
  • 1
  • What is `**INTEGER auto_increment**`?. Can you share the exact query which you have tried? What was the result when executing `select @column_sql1`? BTW can you share the error msg by editing your post. – James Nov 19 '19 at 05:49
  • You may want to describe what you are trying to do, e.g. why you want to do this, we might find a different approach. Within MySQL, you basically never need a pivot (and it is e.g. not possible to access the resultset from within mysql, so the columnnames are not relevant), as you can do everything you want to query on the not-pivoted data. It is (if at all) only relevant when you sent it to the client (e.g. to display it), and basically every client (e.g. php) can access the columns by index (and if you need help with that, specify your development language). – Solarflare Nov 19 '19 at 07:16
  • If you want to pursue with your approach, you can use something like [GROUP_CONCAT numbering](https://stackoverflow.com/q/38449981) (or a window function if you are on MySQL 8), but again, you are probably solving a problem that should not exist. – Solarflare Nov 19 '19 at 07:30
  • @James Error message added. I also tried to create a secondary table that had only a count to join, I tried concating value + Id, but like I said the ID column for the temp table didn't number per employee, so it wasn't a good count. Row_number gave me the same error as the auto_increment. I think that might only be for newer versions of mysql. – jared Nov 19 '19 at 13:42
  • @Solarflare In our database we have a table where each person_id has up to 6 values in the table. Our client needs an export with one row per person_id, and 6 columns for the values, and 6 columns for the dates. This is part of a much larger export, so a pivot is necessary. SQL version 5.6 – jared Nov 19 '19 at 13:49
  • Solarflare, your concat numbering suggestion (concat(value, x:=x+1)) did work. Thanks for the help! – jared Nov 20 '19 at 00:04

0 Answers0