Forum Stats

  • 3,872,457 Users
  • 2,266,425 Discussions
  • 7,911,207 Comments

Discussions

Pivot help - Strings not values

Mr Neil
Mr Neil Member Posts: 113 Blue Ribbon
edited May 24, 2016 9:48PM in SQL & PL/SQL

Hey all,

I need some direction on how to achieve the following outcome.

I've used 11g pivot before with values that suited the (mandatory) aggrigate function, but I can't see a solution for this. Any help appreciated.

   

PROJECTWORKERTHE_DATE
Project_AJohn05/23/2016
Project_AMary05/23/2016
Project_AMary05/24/2016
Project_ASteve05/24/2016
Project_AMary05/25/2016
Project_AMary05/26/2016
Project_BJohn05/23/2016
Project_BSteve

05/24/2016

  

WORKER05/23/201605/24/201605/25/201605/26/2016
JohnProject_A
JohnProject_B
Steve Project_A
Steve Project_B
MaryProject_AProject_AProject_AProject_A

Thanks.

NT

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,722 Red Diamond
    edited May 24, 2016 7:19PM

    Hi,

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

    Explain, using specific examples, how you get those results from that data.

    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ:

    Use the analytic ROW_NUMBER function to assign numbers 1, 2, 3, ... to each row, with a separate series for each worker and date.  Each distinct combination of worker and assigned number will result in a row of output.

  • CarlosDLG
    CarlosDLG Member Posts: 1,378 Gold Trophy
    edited May 24, 2016 8:03PM

    You will usually get quicker and more accurate answers if you post create table statements and inserts with some sample data, along with an explanation of the specific problem you are facing.

    Here is one way to do it:

    WITH test_data as
    (
    SELECT 'Project_A' project,'John' worker,date '2016-05-23' the_date FROM DUAL union all
    SELECT 'Project_A','Mary',date '2016-05-23'  FROM DUAL union all
    SELECT 'Project_A','Mary',    date '2016-05-24' FROM DUAL union all
    SELECT 'Project_A','Steve',date '2016-05-24' FROM DUAL union all
    SELECT 'Project_A','Mary',date '2016-05-25' FROM DUAL union all
    SELECT 'Project_A','Mary',date '2016-05-26'  FROM DUAL union all
    SELECT 'Project_B','John',date '2016-05-23' FROM DUAL union all
    SELECT 'Project_B','Steve',date '2016-05-24' FROM DUAL
    )
    select worker,c23,c24,c25,c26 from
    (
      SELECT t.*, dense_rank() over (PARTITION BY worker ORDER BY project) AS rn
      FROM test_data t
    )
    pivot
    (
      MAX(project) FOR the_date in (date '2016-05-23' AS C23,date '2016-05-24' AS C24,date '2016-05-25' AS C25,date '2016-05-26' AS C26)
    )
    ORDER BY worker;
    
    

    Results:

    WORKERC23C24C25C26
    JohnProject_A
    JohnProject_B
    MaryProject_AProject_AProject_AProject_A
    Steve Project_A
    Steve Project_B
  • Paulzip
    Paulzip Member Posts: 8,799 Blue Diamond
    edited May 24, 2016 9:48PM

    You cannot dynamically generate your columns (e.g. generate your column names based on the data) without using special techniques (dynamic SQL), SQL has to have static columns at execution time.

    Here's another way to do it.

    select worker, d1, d2, d3, d4

    from

    (

      select project, worker,

             dense_rank() over (order by the_date) as date_rank,

             dense_rank() over (order by project) as project_rank

      from test_data t

    )

    pivot (

      max(project)

      for date_rank in (1 as D1, 2 as D2, 3 as d3, 4 as d4)

    )

    order by worker

    WORKERD1D2D3D4
    JohnProject_A
    JohnProject_B
    MaryProject_AProject_AProject_AProject_A
    SteveProject_A
    SteveProject_B
This discussion has been closed.