Create a view by using row data as column names

Question

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.

Found a related article, that provides some answers: stackoverflow.com/questions/1599788/… — Debreczeni András
– Debreczeni András, Commented Jun 6, 2014 at 7:48
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? — user1621001
– user1621001, Commented Jun 6, 2014 at 8:44

Ankit Bajpai · Accepted Answer · 2014-06-06 09:31:52Z

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

answered Jun 6, 2014 at 9:31

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vaidehi Chhajer Over a year ago

The Names are not known before hand and can be more more than 2.

Collectives™ on Stack Overflow

Create a view by using row data as column names

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related