Skip to main content
Database Administrators

Return to Question

edited tags
Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663
added 400 characters in body; edited tags; edited title
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

Need to transpose a database table Transposing data

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real Real data in the database is really big so I don't expect to use Excel to solve this problem. Can Can this be done by using sqlSQL or is there any tool that can do this? Thank you.

[Original table]Original table

  1. ID price_date_1 price_date_2 ... price_date_30 price_date_31
  2. A 1 2 ... 0 1
  3. B 2 3 ... 1 2
+----+--------------+--------------+-----+---------------+---------------+
| ID | price_date_1 | price_date_2 | ... | price_date_30 | price_date_31 |
+----+--------------+--------------+-----+---------------+---------------+
| A  |            1 |            2 | ... |             0 |             1 |
| B  |            2 |            3 | ... |             1 |             2 |
+----+--------------+--------------+-----+---------------+---------------+

[New Table]New table

  1. Price A B
  2. price_date_1 1 2
  3. price_date_2 2 3
  4. ......
  5. price_date_30 0 1
  6. price_date_31 1 2
+---------------+---+---+
|     Price     | A | B |
+---------------+---+---+
| price_date_1  | 1 | 2 |
| price_date_2  | 2 | 3 |
| ...           |   |   |
| price_date_30 | 0 | 1 |
| price_date_31 | 1 | 2 |
+---------------+---+---+

Need to transpose a database table

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to use Excel to solve this problem. Can this be done by using sql or is there any tool that can do this? Thank you.

[Original table]

  1. ID price_date_1 price_date_2 ... price_date_30 price_date_31
  2. A 1 2 ... 0 1
  3. B 2 3 ... 1 2

[New Table]

  1. Price A B
  2. price_date_1 1 2
  3. price_date_2 2 3
  4. ......
  5. price_date_30 0 1
  6. price_date_31 1 2

Transposing data

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to use Excel to solve this problem. Can this be done by using SQL or is there any tool that can do this?

Original table

+----+--------------+--------------+-----+---------------+---------------+
| ID | price_date_1 | price_date_2 | ... | price_date_30 | price_date_31 |
+----+--------------+--------------+-----+---------------+---------------+
| A  |            1 |            2 | ... |             0 |             1 |
| B  |            2 |            3 | ... |             1 |             2 |
+----+--------------+--------------+-----+---------------+---------------+

New table

+---------------+---+---+
|     Price     | A | B |
+---------------+---+---+
| price_date_1  | 1 | 2 |
| price_date_2  | 2 | 3 |
| ...           |   |   |
| price_date_30 | 0 | 1 |
| price_date_31 | 1 | 2 |
+---------------+---+---+
Source Link
user16388
user16388
  • 13
  • 2

Need to transpose a database table

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to use Excel to solve this problem. Can this be done by using sql or is there any tool that can do this? Thank you.

[Original table]

  1. ID price_date_1 price_date_2 ... price_date_30 price_date_31
  2. A 1 2 ... 0 1
  3. B 2 3 ... 1 2

[New Table]

  1. Price A B
  2. price_date_1 1 2
  3. price_date_2 2 3
  4. ......
  5. price_date_30 0 1
  6. price_date_31 1 2