Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    13

    Unanswered: Modify the output of the query?

    Hi All,

    Can we modify the output as below in Sybase?

    Volume As Of Day
    ----------------------- --------------------------
    2158827 Dec 9 2088 12:00AM
    2234392 Dec 8 2088 12:00AM
    2235573 Dec 5 2088 12:00AM
    1821442 Dec 4 2088 12:00AM
    2650401 Dec 3 2088 12:00AM
    2790244 Dec 2 2088 12:00AM
    2541708 Dec 1 2088 12:00AM

    Need to modify the query which gives the following output

    TABLE_NAME 01-DEC-08 02-DEC-08 03-DEC-08 04-DEC-08 05-DEC-08 08-DEC-08 09-DEC-08
    ----------------- ------------ ------------ ------------ ------------ ------------- ------------- -------------
    StifTB 2541708 2790244 2650401 1821442 2235573 2234392 2158827

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ace_friends22
    Can we modify the output as below in Sybase?
    You'd do far better outputting the data as
    Code:
    Date        Volume
    ---------   -----------
    01-DEC-08   2541708 
    02-DEC-08   2790244 
    ...
    I personally would do it this way because databases work with sets of data and this is how sets are represented by them. It is also far simpler to program. You won't end up with string length problems when you have more than a few hundred date values. I also think it's easier to read. If you are determined to keep your original format then I'd use the calling program to do the formatting and not the SQL.

  3. #3
    Join Date
    Mar 2009
    Posts
    13
    Yes,

    Initialliy, it was the output, but now i need it in follodwing format, so that report contain the number of record in each table for last 10 days. If it is 0, then we can check it.

    Date1 Date2 Date3 ....Date10

    Table Name1 countA countB countC ...
    Table Name2 countD 0count countF ...
    Table Name3 countG countH countI ...
    Table Name4 0count countK countL ...

    That's why i need help on this.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    use a case statement e.g.
    Code:
    select shift
    , 'day01'=sum(case when datepart(dd,dt)= 1 then volume end) 
    , 'day02'=sum(case when datepart(dd,dt)= 2 then volume end)
    , 'day03'=sum(case when datepart(dd,dt)= 3 then volume else 0 end)
    , 'day04'=sum(case when datepart(dd,dt)= 4 then volume end)
    , 'day05'=sum(case when datepart(dd,dt)= 5 then volume end)
    , 'day06'=sum(case when datepart(dd,dt)= 6 then volume end)
    , 'day07'=sum(case when datepart(dd,dt)= 7 then volume end)
    , 'other'=sum(case when datepart(dd,dt)> 7 then volume end)
    , 'total'=sum(volume)
    from (select -- start of test data
    0, '20080101', 3.45 union all select
    1, '20080102', 3.45 union all select
    2, '20080103', 6.91 union all select
    0, '20080104', 0.37 union all select
    1, '20080105', 3.83 union all select
    2, '20080106', 7.29 union all select
    0, '20080107', 0.75 union all select
    1, '20080108', 4.21 union all select
    2, '20080109', 7.67 union all select
    0, '20080110', 1.13 union all select
    1, '20080101', 4.59 union all select
    2, '20080102', 8.05 
    )f(shift,dt,volume) -- end test data
    group by shift

Posting Permissions

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