Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    21

    Unanswered: Converting row to column

    I have a table departinfo with following records

    begin_time end_time Name Pieces

    10:00 10:15 PopCorn 3
    10:15 10:30 Biscuits 5
    10:30 10:45 PopCorn 2

    Now I need to run a sql query and the output should be as below :

    begin_time end_time PopCorn Biscuits

    10:00 10:15 3 0
    10:15 10:30 0 5
    10:30 10:45 2 0

    I need a single sql. Please help

  2. #2
    Join Date
    Jan 2004
    Posts
    51

    Re: Converting row to column

    Originally posted by dss
    I have a table departinfo with following records

    begin_time end_time Name Pieces

    10:00 10:15 PopCorn 3
    10:15 10:30 Biscuits 5
    10:30 10:45 PopCorn 2

    Now I need to run a sql query and the output should be as below :

    begin_time end_time PopCorn Biscuits

    10:00 10:15 3 0
    10:15 10:30 0 5
    10:30 10:45 2 0

    I need a single sql. Please help
    you can do it in following way , though I am not sure if it is efficient

    select begin_time,
    end_time,
    case when name="PopCorn" then Pieces
    else 0 end as PopCorn,
    case when name="Biscuits" then Pieces
    else 0 end as Biscuits
    from ..


    If there can be multiple records for same begin_time , end_time then you may have to use group by and then sum on pieces

    Sushant

  3. #3
    Join Date
    Jan 2004
    Posts
    21

    Re: Converting row to column

    This is giving syantax error in Sybase SQL 11,
    Incorrect syntax near 'Name'.

  4. #4
    Join Date
    Jan 2004
    Posts
    51

    Re: Converting row to column

    Originally posted by dss
    This is giving syantax error in Sybase SQL 11,
    Incorrect syntax near 'Name'.
    Can you check the name of the column. In the query I used 'name', did you correct it to 'Name'?

  5. #5
    Join Date
    Jan 2004
    Posts
    21
    Yes I did correct it. But I am still getting Syantax error.

  6. #6
    Join Date
    Jan 2004
    Posts
    51
    Originally posted by dss
    Yes I did correct it. But I am still getting Syantax error.
    Maybe then the sybase 11 does not support this syntax. Can you copy/paste the query that you are using?

  7. #7
    Join Date
    Jan 2004
    Posts
    21
    This is my table

    1> select *
    2> from MyTable
    3> go
    begin_time end_time Name Pieces
    -------------------- ----------------- ---------- -----------
    Jan 1 1900 10:00AM Jan 1 1900 10:15AM PopCorn 3
    Jan 1 1900 10:15AM Jan 1 1900 10:30AM Biscuits 5
    Jan 1 1900 10:30AM Jan 1 1900 10:45AM PopCorn 2

    (3 rows affected)

    When I try to execute the sql
    1> select begin_time,
    2> end_time,
    3> case when Name = "PopCorn" then Pieces
    4> else 0 end as 'PopCorn'
    5> from MyTable
    6> go

    I am getting this error
    Msg 102, Level 15, State 1:
    Line 3:
    Incorrect syntax near 'Name'.

  8. #8
    Join Date
    Jan 2004
    Posts
    51
    Originally posted by dss
    This is my table

    1> select *
    2> from MyTable
    3> go
    begin_time end_time Name Pieces
    -------------------- ----------------- ---------- -----------
    Jan 1 1900 10:00AM Jan 1 1900 10:15AM PopCorn 3
    Jan 1 1900 10:15AM Jan 1 1900 10:30AM Biscuits 5
    Jan 1 1900 10:30AM Jan 1 1900 10:45AM PopCorn 2

    (3 rows affected)

    When I try to execute the sql
    1> select begin_time,
    2> end_time,
    3> case when Name = "PopCorn" then Pieces
    4> else 0 end as 'PopCorn'
    5> from MyTable
    6> go

    I am getting this error
    Msg 102, Level 15, State 1:
    Line 3:
    Incorrect syntax near 'Name'.
    What is the data type for column Name.

    try following for case

    case Name
    when "PopCorn" then Pieces
    when "Biscuits" then 0
    end PopCorn


    Also try your query without single quote after as i.e. as PopCorn

  9. #9
    Join Date
    Jan 2004
    Posts
    21
    No its not working..
    Datatype for Name is Varchar

    I tried all options around name with quotes, with single quotes, with no quotes,

    Are you able to create the same sql on your server.

  10. #10
    Join Date
    Jan 2004
    Posts
    51
    Originally posted by dss
    No its not working..
    Datatype for Name is Varchar

    I tried all options around name with quotes, with single quotes, with no quotes,

    Are you able to create the same sql on your server.
    I do not have access to the server but I am quite confident that query I mentioned would work in sybase 12.0

  11. #11
    Join Date
    Jan 2004
    Posts
    51
    Originally posted by sushant
    I do not have access to the server but I am quite confident that query I mentioned would work in sybase 12.0
    Other way (not very good way) of doing this is

    select begin_time, end_time, Pieces Popcorn, 0 Buiscuits
    from MyTable
    where Name="PopCorn"
    union
    select begin_time, end_time, 0 , Pieces
    from MyTable
    where Name="Buiscuits"
    order by begin_time

  12. #12
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I'm think what you're looking for is a 'cross-tab' query. Try searching these forums for crosstab and/or try google.
    Thanks,

    Matt

Posting Permissions

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