rows to columns

Raunaq · Feb 12, 2015 9:02AM

hi ,

i am on 11g

I have a query which returns data inthe following format ,

YEAR COUNT

2015 445

2015 115

2015 577

2015 11

2015 28

2015 30

2015 270

2015 87

2015 62

2015 68

2015 15

2015 10

2015 5

2015 12

2015 34

2015 118

2015 19

2014 1541

2014 25

2014 581

2014 22

2014 18

2014 59

2014 7828

2014 7

2014 4

Now i want all the data to be displayed in one row for each year

Example:

col1 col2 col3 col4......

2015 445 115 577......

2014 25 581...

2013....

.

and so on ....

i am getting the above data by running the following query:

{code}

select to_char(create_dt,'YYYY') "YEAR",

COUNT(*) Count

FROM SCH1.Tab1 , table2

Where tab1.c1=table2.c1

group by

to_char(create_dt,'YYYY') ,

tab1.col1

Hoek · Feb 12, 2015 9:26AM

If you search this forum you'll get lots of examples.

See also the FAQ:

Frank Kulash · Feb 12, 2015 9:27AM

Hi,

You can use the aggregate LISTAGG function for that.

In a sub-query, do more or less what you're doing now, grouping by the year and col1. Then, in a super-query, use LISTAGG, groupig only by year.

If you'd like help, post CREATE TABLE and INSERT statemements for a little sample data, and the exact results you want from that data.

Post your best attempt, using LISTAGG (or any of the other techniques shown in the Forum FAQ: ), and a specific question about where you're getting stuck.

See

Raunaq · Feb 13, 2015 5:57AM

I need values as different columns , so i believe LISTAGG will not work in my case.

As LISTAGG will make all values in a SINGLE column right?

BluShadow · Feb 13, 2015 6:04AM

So what have you tried from the examples in the FAQ? What's not answered by that?

Frank Kulash · Feb 13, 2015 7:03AM

Hi,

Raunaq wrote:

I need values as different columns , so i believe LISTAGG will not work in my case.
As LISTAGG will make all values in a SINGLE column right?

You're right that LISTAGG will put all the values into a single column. You can format the data so that it looks like separate columns. For example, the output could be

year  col2  col3  col4  ...
----  ----  ----  ----  ...
2015    445  115   577  ...
2014     25        581  ...
2013    123   45     6  ...

which would look like 3 rows and 4 columns (not counting the "..."s), but which would actually be 5 rows and 2 columns. At the time you write the query, you do not need to know how many apparent columns like col2, col3, col4, ..., There may be 3 columns, or 4, or 5, or 345.

If you really need col2, col3, col4, ... all in separate columns, then use one of the other techniques found on page cited earlier. The exact number of columns in the output must be hard-coded into the query.

Utsav · Feb 13, 2015 7:40AM

Raunaq Bhai,

Dekho kuch is tarah se kaam bane,

It could be use a sample code

drop table tmpa;
create table tmpa
as
with t(cola,colb) as (select  2015  , 'a' from dual union all
select 2015 , 'a'  from dual union all
select 2015, 'a'  from dual union all
select 2015, 'a'  from dual union all
select 2015, 'a'  from dual union all
select 2015, 'b'  from dual union all
select 2015,'b'  from dual union all
select 2015, 'b'  from dual union all
select 2015, 'c'  from dual union all
select 2015,'c'  from dual union all
select 2014, 'a'  from dual union all
select 2014,'b'  from dual)
select cola,colb  
 From t; 
SELECT *
  FROM tmpa PIVOT (COUNT (colb) FOR (colb) IN ('a', 'b', 'c'))

Output

      COLA        'a'        'b'        'c'
---------- ---------- ---------- ----------
      2014          1          1          0
      2015          5          3          2


SQL>

Forum Stats

Quick Links

Categories

rows to columns

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

rows to columns

Answers