Help in this sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help in this sql

  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Help in this sql

    Hi I have a sql and output like this

    select
    b.LIC_NO,TO_CHAR(a.ro_dtime,'YYYY') RO_DTIME,A.RO_NO
    from MSS_RO_HDR a, Mss_V_car_records b
    WHERE a.ro_dtime between '01-JAN-2000' AND '01-JAN-2007'
    AND a.CARD_ID=b.ID
    GROUP BY b.LIC_NO,TO_CHAR(A.RO_DTIME,'YYYY'),a.ro_no;

    My current output is like this

    LIC_NO RO_DTIME RO_NO
    GW9896T 2004 RA0044874
    GW9896T 2004 RA0047489
    GW9896T 2005 RA0056049
    GW9896T 2005 RA0056216
    GW9896T 2006 RA0066809
    GW9896T 2006 RA0069883
    GY694H 2004 RA0053667
    GY694H 2004 RA0053799
    GY694H 2005 RA0054237
    GY694H 2005 RA0054571
    GY694H 2006 RA0071338
    GY694H 2006 RA0079215

    My required Output is like this:

    LIC_NO 2004 2005 2006
    GW9896T RA0044874,RA0047489 RA0056049,RA0056216 RA0066809,RA0069883
    GY694H RA0053667,RA0053799 RA0054237,RA0054571 RA0071338,RA0079215

  2. #2
    Join Date
    Jan 2007
    Posts
    231
    Kumar instead of showing required output, You can specify I need LIC_no,year and so on.. for particular user/all user something like that so that all can understand your actual need.

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    This is called PIVOTING and is generally bad situation.
    Even if you manage to make such query (there are several ideas, more or less successful) it will be extremely hard then to process the data ( to format, aggregate etc)

    Maybe it's better to get the data as they are, and let the application interface to format them as needed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width