-1

I have a table is SQL similar to below:

Date ticker price
1 X 100
2 X 101
3 X 102
1 Y 50
2 Y 51
3 Y 52

and would like to change it to the following table

Date X Y
1 100 50
2 101 51
3 102 52

I know this can be done using Unstack in pandas, but I am not sure how to do this in MySQL. Also, I need to assign the column headings dynamically, since I have hundreds of tickers (X, Y, Z, ...).

Any help will be appreciated.

Thank you.

Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
Data1000
  • 1
  • Does this answer your question? [Mysql, reshape data from long / tall to wide](https://stackoverflow.com/questions/2255640/mysql-reshape-data-from-long-tall-to-wide) – Trenton McKinney Aug 12 '21 at 18:59
  • [SO: mysql long to wide](https://www.google.com/search?q=mysql+long+to+wide+site:stackoverflow.com&sxsrf=ALeKk002qPqW6VmV3OjI7SFqxhEPAiTjHg:1628794746641&sa=X&ved=2ahUKEwjh4qOilazyAhXHEzQIHSg2DnsQrQIoBHoECAcQBQ&biw=1920&bih=918) & [dba: mysql long to wide](https://www.google.com/search?q=mysql+long+to+wide+site:dba.stackexchange.com&sxsrf=ALeKk002qPqW6VmV3OjI7SFqxhEPAiTjHg:1628794746641&sa=X&ved=2ahUKEwjh4qOilazyAhXHEzQIHSg2DnsQrQIoA3oECAYQBA&biw=1920&bih=918) – Trenton McKinney Aug 12 '21 at 19:01
  • As an FYI, SO is not a coding service. Please read the following documentation, then [edit], and rephrase the question. [Take the Tour](https://stackoverflow.com/tour), [How to ask a good question](https://stackoverflow.com/help/how-to-ask), & [On Topic](https://stackoverflow.com/help/on-topic). Always provide a [mre] with **code, data, errors, current & expected output, as [formatted text](https://stackoverflow.com/help/formatting)** & you're expected to [try to solve the problem first](https://meta.stackoverflow.com/questions/261592). – Trenton McKinney Aug 12 '21 at 19:02
  • @ Trenton... there are two suggested answers in the link, but they both rely on me specifying the new table's column headings. I need a solution where column headings are automatically generated from the data. – Data1000 Aug 12 '21 at 19:09

1 Answers1

0

I found this code snippet on a similar data that seems to work for my scenario. The values get altered a bit for me for some reason, but if I round them, they approach the original values.

CREATE TABLE if not exists Meeting
(
ID INT,
Meeting_id INT,
field_key VARCHAR(100),
field_value VARCHAR(100)
);

INSERT INTO Meeting(ID,Meeting_id,field_key,field_value) VALUES (1, 1,'first_name' , John); INSERT INTO Meeting(ID,Meeting_id,field_key,field_value) VALUES (2, 1,'last_name' , 'Jones'); INSERT INTO Meeting(ID,Meeting_id,field_key,field_value) VALUES (3, 1,'occupation' , 'engineer'); INSERT INTO Meeting(ID,Meeting_id,field_key,field_value) VALUES (4,2,'first_name' , Jack); INSERT INTO Meeting(ID,Meeting_id,field_key,field_value) VALUES (5,2,'last_name' , 'Doe'); INSERT INTO Meeting(ID,Meeting_id,field_key,field_value) VALUES (6,2,'occupation' , 'engineer');

select * from meeting;

SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when field_key = ''', field_key, ''' then field_value end) ', field_key ) ) INTO @sql FROM Meeting; SET @sql = CONCAT('SELECT Meeting_id, ', @sql, ' FROM Meeting GROUP BY Meeting_id');

 PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Data1000
  • 1