0

I have an SQL table like the following:

---------------------------
| Timestamp | Value | Name|
---------------------------
| 12345678  | 2     | abc |
---------------------------
| 12345678  | 3     | abc |
---------------------------
| 78910111  | 4     | cde |
--------------------------
| 56789101  | 1     | abc |
---------------------------
| 56789101  | 2     | cde |
---------------------------

I need to transform it into something like this:

-------------------------
| Timestamp | abc | cde |
-------------------------
| 12345678  |  5  |  0  |
-------------------------
| 56789101  |  1  |  2  |
-------------------------
| 78910111  |  0  |  4  |
-------------------------

Where values in the above table are sums for a particular Name at a particular timestamp the names in the lower table are not known and can be dynamic. I need a SQL query to do this.

Alex
  • 781
  • 10
  • 23
Vaidehi Chhajer
  • 27
  • 2
  • 4
  • Found a related article, that provides some answers: http://stackoverflow.com/questions/1599788/mysql-query-to-dynamically-convert-row-data-to-columns – Debreczeni András Jun 06 '14 at 07:48
  • 1
    what if you have 1000+ Name in the table ? – Abhik Chakraborty Jun 06 '14 at 07:53
  • While there is an upper limit of column rows that needs to be watched. If that is not really an issue (you are going to have comfortably less than 1000 columns) and you want to do this dynamically then you need to be looking at Dynamic SQL (for SQL Server) PL/SQL (for Oracle), etc. What system are you using? – ashcanschool Jun 06 '14 at 08:44
  • possible duplicate of [Transpose rows into columns in MySQL](http://stackoverflow.com/questions/4943496/transpose-rows-into-columns-in-mysql) – Strawberry Jun 06 '14 at 09:33
  • 1
    Who upvotes this stuff? – Strawberry Jun 06 '14 at 09:33

1 Answers1

0

You can Try following query:-

SELECT timestamp, SUM(CASE WHEN name = 'abc' THEN VALUE ELSE 0 END) AS abc,
SUM(CASE WHEN name = 'cde' THEN VALUE ELSE 0 END) AS cde
FROM tab
GROUP BY timestamp;

Here is the SQL Fiddle.

http://sqlfiddle.com/#!2/a721f4/8

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40