Thursday, December 18, 2008

Pivot with Connect_By


This is a different version of the pivot that I hadn't seen. I wanted to be able to find it so I listed it here. The original code can be found in an article by Younes Naguib.
Column Name_List Format A100

Select Deptno, 
              -- use the substring to get rid of the initial comma
              ----------------------------------------------------
              Substr(Sys_Connect_By_Path(Ename, ','),2)
              As
       Name_List
  From
      (
       Select Ename,
              Deptno,
              --------------------------------------------------
                    Count(*) Over ( Partition By Deptno )
                    As
              Cnt,
              --------------------------------------------------
                    Row_Number () Over ( Partition By Deptno
                                             Order By Ename  )
                    As
              Seq
         From Emp
      )
 Where Seq = Cnt  -- only pick up the first one
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
      Start With  Seq     = 1
 Connect By Prior Seq + 1 = Seq
        And Prior Deptno  = Deptno; 

(TinyURL: http://tinyurl.com/sql-pivot)

4 comments:

  1. Lynn-- I've got a IT toolbox FAQ for Oracle pivots
    http://tinyurl.com/2c92qo
    that also links to a page on Tim Hall's oracle-base website. There Tim also links to a similar SYS_CONNECT_BY_PATH solution.

    ReplyDelete
  2. SELECT * FROM daily_sales_activity PIVOT (MIN(visit_type) FOR visit_date IN (‘1/Sep/2014′ ,’2/Sep/2014′ ,’3/Sep/2014′ ,’4/Sep/2014′ ,’5/Sep/2014′ ,’6/Sep/2014′,’7/Sep/2014′, ‘8/Sep/2014′ , ‘9/Sep/2014′,’10/Sep/2014′ ,’11/Sep/2014′ ,’12/Sep/2014′ ,’13/Sep/2014′ ,’14/Sep/2014′ ,’15/Sep/2014′ , ’16/Sep/2014′ ,’17/Sep/2014′,’18/Sep/2014′,’19/Sep/2014′,’20/Sep/2014′,’21/Sep/2014′,’22/Sep/2014′,’23/Sep/2014′,’24/Sep/2014′,’25/Sep/2014′,’26/Sep/2014′,’27/Sep/2014′,’28/Sep/2014′,’29/Sep/2014′,’30/Sep/2014′ )) where custname is not null ” + whereclause + ” ORDER BY CUSTNAME

    I have this query i want date as a header in column without hardcode kindly help me out …

    ReplyDelete
    Replies
    1. Bilal, I apologize that your comment didn't come through to my email and I am just seeing it. I do have a solution to this. Let me know if you need it right away. If not, I'll put it on my list of things to get written up.

      Delete
  3. If anyone needs to do something like what Bilal has requested, see this link: http://my-oracle-10g-tips.blogspot.com/2008/05/creating-column-headings-from-data.html

    ReplyDelete