Post reply

Results need to be Fields

  • August 27, 2003 at 8:49 am

    #80871

    In another words if I run this query ex:

    select day(callwhen) as day, count(day) from billable_transactions group by day(callwhen) order by day(callwhen)

    I need the result DAY as a field ex:

    1 2 3 4 5 6

    instead

    1

    2

    3

    4

    5

    any idea?

    thanks in advance for any help

    Nelson

  • August 27, 2003 at 5:57 pm

    #471718

    How about,

    select day(callwhen) as day

    from billable_transactions

    group by day(callwhen)

    UNION

    select count(callwhen)

    from billable_transactions

    group by day(callwhen)

    order by day(callwhen)

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • August 28, 2003 at 6:27 am

    #471780

    this helps but I need in horizontal position not in vertical ex:

    day 1 2 3 4 5 6 7 8 9

    count 20 34 55 67 78 33 44 55 66

    is there any way the results show in that position?

    thanks

    Nelson

  • August 28, 2003 at 6:35 am

    #471781

    You'd probably have to put the results into a temp table and then do something like the old MS Acess crosstab query.

    I think I saw a script that does something just like this in the script library.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • August 28, 2003 at 6:55 am

    #471793

    declare @table1 table ([Day] int,[Count] int)

    declare @table2 table (day1 int, day2 int, day3 int, day4 int, day5 int)-- etc...

    insert into @table1

    select day(callwhen) as day,

    count(day)

    from billable_transactions

    group by day(callwhen)

    order by day(callwhen)

    insert into @table2 default values

    update @table2

    set [day1] = (select [count] from @table1 where [Day] = 1),

    [day2] = (select [count] from @table1 where [Day] = 2),

    [day3] = (select [count] from @table1 where [Day] = 3),

    [day4] = (select [count] from @table1 where [Day] = 4)

    -- , Day5 etc...

    select * from @table2

    ---

    It's not very elegant, but will do the job.

    Alternatives are using dynamic SQL, temp tables and pivoting the results.

    ---

  • August 28, 2003 at 7:45 am

    #471814

    Hi Nelson,

    here is a brilliant explanation on how do to crosstab queries in SQL Server.

    http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply