Results 1 to 11 of 11
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: Need help with PIVOT

    Let’s say I have some data like this in a table.

    Time, Data, Value
    5:00 AM, Level, 45
    5:00 AM, Flow, 2
    6:00 AM, Level, 40
    6:00 AM, Flow, 4
    7:00 AM, Level, 30
    7:00 AM, Flow, 5
    8:00 AM, Level, 25
    8:00 AM, Flow, 6

    I’d pull the data with something like this:

    SELECT Time, Data, Value FROM History WHERE Data = ‘Level’ OR Data = ‘Flow’ AND Time >= ‘5:00 AM’ AND Time <= ‘8:00 AM’

    For the life of me, I can not figure out how to pull the data so that it’ll look like this:

    Time, Level, Flow
    5:00 AM, 45, 2
    6:00 AM, 40, 4
    7:00 AM, 30, 5
    8:00 AM, 25, 6

    I know I’d use the PIVOT command, but I’m not having any luck getting the syntax correct.

    I'd appreciate any help I can get!

    Thanks,
    Jason

  2. #2
    Join Date
    May 2011
    Posts
    2
    SELECT Time, Data, Value FROM History WHERE Data = ‘Level’ OR Data = ‘Flow’ as 'Data' AND Time >= ‘5:00 AM’ AND Time <= ‘8:00 AM’ as 'Time'




    Quote Originally Posted by Lakee911 View Post
    Let’s say I have some data like this in a table.

    Time, Data, Value
    5:00 AM, Level, 45
    5:00 AM, Flow, 2
    6:00 AM, Level, 40
    6:00 AM, Flow, 4
    7:00 AM, Level, 30
    7:00 AM, Flow, 5
    8:00 AM, Level, 25
    8:00 AM, Flow, 6

    I’d pull the data with something like this:

    SELECT Time, Data, Value FROM History WHERE Data = ‘Level’ OR Data = ‘Flow’ AND Time >= ‘5:00 AM’ AND Time <= ‘8:00 AM’

    For the life of me, I can not figure out how to pull the data so that it’ll look like this:

    Time, Level, Flow
    5:00 AM, 45, 2
    6:00 AM, 40, 4
    7:00 AM, 30, 5
    8:00 AM, 25, 6

    I know I’d use the PIVOT command, but I’m not having any luck getting the syntax correct.

    I'd appreciate any help I can get!

    Thanks,
    Jason




    Online Diploma
    Last edited by healdem; 05-16-11 at 10:52.

  3. #3
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by johnadam View Post
    SELECT Time, Data, Value FROM History WHERE Data = ‘Level’ OR Data = ‘Flow’ as 'Data' AND Time >= ‘5:00 AM’ AND Time <= ‘8:00 AM’ as 'Time'
    I'm getting an "Incorrect Syntax near the keyword AS." I've never seen it done like this before .. can you explain it a bit?

    Thx

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe johnadam is just using you to put spam in his post. i would not pay much attention to his "solution".

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Lakee911 View Post
    SELECT Time, Data, Value FROM History WHERE Data = ‘Level’ OR Data = ‘Flow’ AND Time >= ‘5:00 AM’ AND Time <= ‘8:00 AM’

    For the life of me, I can not figure out how to pull the data so that it’ll look like this:

    Time, Level, Flow
    5:00 AM, 45, 2
    6:00 AM, 40, 4
    7:00 AM, 30, 5
    8:00 AM, 25, 6
    If it's only those two "columns" (level and flow) then not using PIVOT might actually be easier.

    Something like this should work:
    Code:
    SELECT time, 
           max(
              case 
                when data = 'Level' then data
                else null
              end) as level, 
           max(
              case 
                when data = 'Flow' then data
                else null
              end) as flow
    FROM your_table
    WHERE Data IN ('Level', 'Flow')
    AND   Time >= '5:00 AM' AND Time <= '8:00 AM'
    GROUP BY time
    (There might be some typos in there, I don't have the time to setup a testing table right now...)

    To understand how this is working run the statement once with the max() and group by things removed then you'll probably understand it better.

  6. #6
    Join Date
    May 2011
    Posts
    5
    Thanks for the post. I had actually simplified the problem a bit for posting purposes, but now that I have it implemented I am getting the following error "Error executing query: Error converting data type varchar to float."

    Code:
    SELECT History.DateTime,
     max(
       case
         when Value = 'EQ_BASIN_LEVEL.Level' then Value
       else null
      end) as Level,
     max(
       case
         when Value = 'WWTP_TOT_INF_FLOW.Flow_Feedback'  then Value
       else null
      end) as Flow
    FROM History
    WHERE Value IN ('Level', 'Flow')
     AND DateTime >= '2011-05-15 5:00:00'
     AND DateTime <= '2011-05-15 8:00:00'
     AND wwRetrievalMode = 'Cyclic'
     AND wwResolution = 3600000
    GROUP BY DateTime
    I kind of see what you're doing here, but supposing I had more data (Level, Flow, Temperature, analytical levels, pump speed, etc) is there a better/easier way to go with the query?

    Thx

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What datatype is the column value

  8. #8
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by shammat View Post
    What datatype is the column value
    I think it's a float type. It's comming from a SCADA system and that's the type of data being recorded. I didn't set it up so I can't be entirely sure.

    Thx

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If it's a float then of course the comparison

    Value = 'WWTP_TOT_INF_FLOW.Flow_Feedback'

    is wrong as you compare a float against a character literal.

    My guess is that you actually want to compare the column named value against the column named WWTP_TOT_INF_FLOW.Flow_Feedback.

    In that case you should get rid of the single quotes. They are only needed for character literals. Column names do not need quotes - at least they never need single quotes.

    But there is no table named WWTP_TOT_INF_FLOW in your select statement, so where should that come from?

  10. #10
    Join Date
    May 2011
    Posts
    5
    There is some confusion here and for that I apologize. I shouldn't have simplified the situation.

    Here's the actual query that I started with and it has the data that I need/want.

    Code:
    SELECT DateTime, TagName, Value
    FROM History
    WHERE TagName = 'WWTP_TOT_INF_FLOW.Flow_Feedback' 
     OR Tagname = 'EQ_BASIN_LEVEL.Level'
     AND DateTime >= '2011-05-15 5:00:00'
     AND DateTime <= '2011-05-15 8:00:00'
     AND wwRetrievalMode = 'Cyclic'
     AND wwResolution = 3600000
    and what I get are the following results:

    Code:
    DateTime,TagName,Value
    5/15/2011 5:00:00 AM,EQ_BASIN_LEVEL.Level,0.0399999991059303
    5/15/2011 5:00:00 AM,WWTP_TOT_INF_FLOW.Flow_Feedback,10.6199998855591
    5/15/2011 6:00:00 AM,EQ_BASIN_LEVEL.Level,0.0299999993294477
    5/15/2011 6:00:00 AM,WWTP_TOT_INF_FLOW.Flow_Feedback,10.7700004577637
    5/15/2011 7:00:00 AM,EQ_BASIN_LEVEL.Level,0.0499999970197678
    5/15/2011 7:00:00 AM,WWTP_TOT_INF_FLOW.Flow_Feedback,8.11999988555908
    5/15/2011 8:00:00 AM,EQ_BASIN_LEVEL.Level,0.0499999970197678
    5/15/2011 8:00:00 AM,WWTP_TOT_INF_FLOW.Flow_Feedback,6.28000020980835
    Note that the data in the TagName does contain a period.


    As you can see for each TagName (which I was calling Data before), I have two rows at the same time. Ideally, I'd list many many TagNames and those would be the columns and the DateTime (which I was calling Time before) would be on each row, like this:

    Code:
    DateTime,EQ_BASIN_LEVEL.Level,WWTP_TOT_INF_FLOW.Flow_Feedback
    5/15/2011 5:00:00 AM,0.0399999991059303,10.6199998855591
    5/15/2011 6:00:00 AM,0.0299999993294477,10.7700004577637
    5/15/2011 7:00:00 AM,0.0499999970197678,8.11999988555908
    5/15/2011 8:00:00 AM,0.0499999970197678,6.28000020980835
    How do I do this knowing that I might end up with a total of 6 or 7 TagNames for columns at some point.

    Thanks and sorry for the confusion!

  11. #11
    Join Date
    May 2011
    Posts
    2
    Quote Originally Posted by Lakee911 View Post
    I'm getting an "Incorrect Syntax near the keyword AS." I've never seen it done like this before .. can you explain it a bit?

    Thx

    Are you using double quote after As=? use only single quote like this
    AS='Date'
    First try to use only one condition without OR.

Posting Permissions

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