Sql Data Manipulation

Question

I have data in a table in following manner

tablename : history

page_url       verified         day
------------------------------------------
some_url1      true             2012-06-25
some_url2      true             2012-06-24
some_url1      false            2012-06-25
some_url2      false            2012-06-24

This is like using the day field values as field names in another table

I need the output in the following manner :

page_url           2012-06-25         2012-06-24
------------------------------------------------
some_url1          true               false  
some_url2          true               false

Is it possible by using pl/sql or stored procedures ??

Do you want a result set that has *exactly* those columns? Or do you want the columns to be determined by the data? *(So, you might have 3 columns now, or 10 columns later when more data has been inserted?)* — MatBailie, Jun 28 '12 at 07:58
@Dems, I want the columns to be determined by the data. This table is having the data of around 90 days. — Saurabh Saxena, Jun 28 '12 at 08:36
@SaurabhSazena - Then you need to write PLSQL that will write a SQL query (using MahmoudGamal's answer as a template) and execute that. PLSQL does *not* allow a result set to have an unknown number of columns. — MatBailie, Jun 28 '12 at 08:43

score 1 · Accepted Answer · answered Jun 28 '12 at 08:01

What you are looking for here is pivoting your columns. If these columns are limited like in your sample data, then you can do this with a simple CASE statement like this:

SELECT page_url, 
   MAX(CASE WHEN day = '2012-06-25' THEN verified END) as '2012-06-25',
   MAX(CASE WHEN day = '2012-06-24' THEN verified END) as '2012-06-24'
FROM tableName
GROUP BY page_url

Sql Data Manipulation

1 Answers1