SQL Server Performance

Splitting a single column into two / three columns using Select statement

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by GMAN, Apr 29, 2008.

  1. GMAN New Member

    Hi,
    I am eagerly looking for the help on the subject.
    I have a three columns in my table.
    TimeId (Int) – Primary Key Column
    AllTime (DateTime) – Contains Good Time, Bad Time, etc.
    TimeFlag (Number) – It contains the value 1 = Good Time, 2 = Bad Time, etc.
    I want to generate a report as follows.
    TimeID Date GoodTime BadTime
    I want to split the AllTime column as Date, GoodTime and BadTime based on the value of TimeFlag.
    Please help me on this issue.
    Thanks in Advance
    G.M. Arun
  2. madhuottapalam New Member

    Check this
    Select GT.TimeID,GT.AllTime as GoodTime,BT.AllTime as BadTime From
    (Select TimeID,AllTime,TImeFlag from YourTable Where TimeFlag=1) GT,(Select TimeID,AllTime,TImeFlag from YourTable Where TimeFlag=2) BT
    where GT.TimeID=BT.TimeID
    Madhu
  3. MichaelB Member

    I just came by this post... I know it is old...
    How about not hitting the data from your table twice but just casing it out? Depending on the size of the table, it should have less IO since it is only having to pass the table once rather than twice.
    Select TimeID,
    case when TimeFlag=1 then AllTime
    else null end as GoodTime,
    case when TimeFlag=2 then AllTime
    else null end as BadTime
    From YourTable
  4. bharathsivam New Member

    In the above query Else statement is not required as datatime columns will be NULL
    Select TimeID,
    case when TimeFlag=1 then AllTime
    end as GoodTime,
    case when TimeFlag=2 then AllTime
    end as BadTime
    From YourTable

Share This Page