Forum Stats

  • 3,874,218 Users
  • 2,266,685 Discussions
  • 7,911,771 Comments

Discussions

rows to columns

Raunaq
Raunaq Member Posts: 458
edited Feb 13, 2015 7:40AM in SQL & PL/SQL

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

Tagged:

Answers

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    edited Feb 12, 2015 9:26AM

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

    See also the FAQ:

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond
    edited Feb 12, 2015 9:37AM

    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
    Raunaq Member Posts: 458
    edited 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
    BluShadow Member, Moderator Posts: 42,555 Red Diamond
    edited Feb 13, 2015 6:04AM

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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond
    edited 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
    Utsav Member Posts: 859 Silver Badge
    edited 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>
    
This discussion has been closed.