Loading ...
Sorry, an error occurred while loading the content.

Pivot issue...

Expand Messages
  • King Wilder
    Hi, I thought I figured this out but I didn t. (I m going to include a lot of code and results and they are better viewed in Courier New for spacing.) I ve
    Message 1 of 3 , Feb 17 5:57 PM
    • 0 Attachment
      Hi,



      I thought I figured this out but I didn't. (I'm going to include a lot of
      code and results and they are better viewed in Courier New for spacing.)



      I've tried the pivot sample (in Books Online) using my data and it returns
      awkward results (see below). I think it has to do with the fact that there
      is no sum in the statement. Essentially I want to pivot on a row where
      there is no Aggregate calculation occurring. Is this possible?



      Here, my raw table results.



      Player FlightID Position StartTee TeeTime

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

      Brian Watts 1 4 1 7:00 AM

      Jeff Holdenfield 1 3 1 7:00 AM

      King Kahanu 1 1 1 7:00 AM

      Mike Hunt 1 2 1 7:00 AM

      Al Chang 1 2 1 7:08 AM

      Doug Furuta 1 3 1 7:08 AM

      Greg Bull 1 4 1 7:08 AM

      Mike Delfino 1 1 1 7:08 AM

      Gene Price 1 1 1 7:16 AM



      (9 row(s) affected)



      This is select statement:



      select teetime,

      (case position when 1 then Player else '' end) as 'Player1',

      (case position when 2 then Player else '' end) as 'Player2',

      (case position when 3 then Player else '' end) as 'Player3',

      (case position when 4 then Player else '' end) as 'Player4'

      from TeeTimesGroups

      where flightid = 1

      group by teetime, player, position

      order by teetime, position



      And here are the results from this query:



      teetime Player1 Player2 Player3 Player4

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

      7:00 AM King Kahanu

      7:00 AM Mike Hunt

      7:00 AM Jeff Holdenfield

      7:00 AM Brian Watts

      7:08 AM Mike Delfino

      7:08 AM Al Chang

      7:08 AM Doug Furuta

      7:08 AM Greg Bull

      7:16 AM Gene Price



      (9 row(s) affected)





      As you can see it almost works, but the names for the same time should be on
      the same row.



      If you want to test this I've included the sql script to build the table and
      add some data.





      if exists (select * from dbo.sysobjects where id =
      object_id(N'[TeeTimesGroups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

      drop table [TeeTimesGroups]

      GO



      CREATE TABLE [TeeTimesGroups] (

      [TeeTimesGroupID] [int] IDENTITY (1, 1) NOT NULL ,

      [Player] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [FlightID] [int] NOT NULL ,

      [Position] [int] NULL ,

      [StartTee] [int] NULL ,

      [TeeTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

      ) ON [PRIMARY]

      GO





      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('John Williams', 1, 1, 1, '7:00 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Sam Rockwell', 1, 2, 1, '7:00 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Irvin Kirschner', 1, 3, 1, '7:00 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('George Clooney', 1, 4, 1, '7:00 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Mike Ovitz', 1, 1, 1, '7:08 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Jeff Foxworthy', 1, 2, 1, '7:08 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Steve Spielberg', 1, 3, 1, '7:08 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Brad Pitt', 1, 4, 1, '7:08 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Kevin Bacon', 1, 1, 1, '7:16 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Jet Li', 1, 2, 1, '7:16 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('Bruce Lee', 1, 3, 1, '7:16 AM')

      INSERT INTO TeeTimesGroups (Player, FlightID, Position, StartTee, TeeTime)
      VALUES ('John Saxon', 1, 4, 1, '7:16 AM')





      Any help is appreciated.





      Thanks,



      King Wilder



      [Non-text portions of this message have been removed]
    • John Warner
      Haven t checked this for syntax errors but see if this is close. SELECT teeTime, MAX(CASE WHEN Position = 1 THEN Player END) AS Player1 MAX(CASE WHEN Position
      Message 2 of 3 , Feb 18 6:43 AM
      • 0 Attachment
        Haven't checked this for syntax errors but see if this is close.

        SELECT teeTime,
        MAX(CASE WHEN Position = 1
        THEN Player
        END) AS Player1
        MAX(CASE WHEN Position = 2
        THEN Player
        END) AS Player2
        MAX(CASE WHEN Position = 3
        THEN Player
        END) AS Player3
        MAX(CASE WHEN Position = 4
        THEN Player
        END) AS Player4
        FROM TeeTimesGroups
        GROUP BY TeeTime;

        John Warner


        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of King Wilder
        > Sent: Saturday, February 17, 2007 8:58 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: [SQLQueriesNoCode] Pivot issue...
        >
        >
        > Hi,
        >
        >
        >
        > I thought I figured this out but I didn't. (I'm going to
        > include a lot of code and results and they are better viewed
        > in Courier New for spacing.)
        >
        >
        >
        > I've tried the pivot sample (in Books Online) using my data
        > and it returns awkward results (see below). I think it has
        > to do with the fact that there is no sum in the statement.
        > Essentially I want to pivot on a row where there is no
        > Aggregate calculation occurring. Is this possible?
        >
        >
        >
        > Here, my raw table results.
        >
        >
        >
        > Player FlightID Position StartTee TeeTime
        >
        > ------------------- --------- ----------- ----------- ---------
        >
        > Brian Watts 1 4 1 7:00 AM
        >
        > Jeff Holdenfield 1 3 1 7:00 AM
        >
        > King Kahanu 1 1 1 7:00 AM
        >
        > Mike Hunt 1 2 1 7:00 AM
        >
        > Al Chang 1 2 1 7:08 AM
        >
        > Doug Furuta 1 3 1 7:08 AM
        >
        > Greg Bull 1 4 1 7:08 AM
        >
        > Mike Delfino 1 1 1 7:08 AM
        >
        > Gene Price 1 1 1 7:16 AM
        >
        >
        >
        > (9 row(s) affected)
        >
        >
        >
        > This is select statement:
        >
        >
        >
        > select teetime,
        >
        > (case position when 1 then Player else '' end) as 'Player1',
        >
        > (case position when 2 then Player else '' end) as 'Player2',
        >
        > (case position when 3 then Player else '' end) as 'Player3',
        >
        > (case position when 4 then Player else '' end) as 'Player4'
        >
        > from TeeTimesGroups
        >
        > where flightid = 1
        >
        > group by teetime, player, position
        >
        > order by teetime, position
        >
        >
        >
        > And here are the results from this query:
        >
        >
        >
        > teetime Player1 Player2 Player3 Player4
        >
        > -------- ------------ ---------- ----------------- ------------
        >
        > 7:00 AM King Kahanu
        >
        > 7:00 AM Mike Hunt
        >
        > 7:00 AM Jeff Holdenfield
        >
        > 7:00 AM Brian Watts
        >
        > 7:08 AM Mike Delfino
        >
        > 7:08 AM Al Chang
        >
        > 7:08 AM Doug Furuta
        >
        > 7:08 AM Greg Bull
        >
        > 7:16 AM Gene Price
        >
        >
        >
        > (9 row(s) affected)
        >
        >
        >
        >
        >
        > As you can see it almost works, but the names for the same
        > time should be on the same row.
        >
        >
        >
        > If you want to test this I've included the sql script to
        > build the table and add some data.
        >
        >
        >
        >
        >
        > if exists (select * from dbo.sysobjects where id =
        > object_id(N'[TeeTimesGroups]') and OBJECTPROPERTY(id,
        > N'IsUserTable') = 1)
        >
        > drop table [TeeTimesGroups]
        >
        > GO
        >
        >
        >
        > CREATE TABLE [TeeTimesGroups] (
        >
        > [TeeTimesGroupID] [int] IDENTITY (1, 1) NOT NULL ,
        >
        > [Player] [varchar] (50) COLLATE
        > SQL_Latin1_General_CP1_CI_AS NULL,
        >
        > [FlightID] [int] NOT NULL ,
        >
        > [Position] [int] NULL ,
        >
        > [StartTee] [int] NULL ,
        >
        > [TeeTime] [varchar] (50) COLLATE
        > SQL_Latin1_General_CP1_CI_AS NULL
        >
        > ) ON [PRIMARY]
        >
        > GO
        >
        >
        >
        >
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('John Williams', 1, 1, 1, '7:00 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Sam Rockwell', 1, 2, 1, '7:00 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Irvin Kirschner', 1, 3, 1, '7:00 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('George Clooney', 1, 4, 1, '7:00 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Mike Ovitz', 1, 1, 1, '7:08 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Jeff Foxworthy', 1, 2, 1, '7:08 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Steve Spielberg', 1, 3, 1, '7:08 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Brad Pitt', 1, 4, 1, '7:08 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Kevin Bacon', 1, 1, 1, '7:16 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Jet Li', 1, 2, 1, '7:16 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('Bruce Lee', 1, 3, 1, '7:16 AM')
        >
        > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
        > StartTee, TeeTime) VALUES ('John Saxon', 1, 4, 1, '7:16 AM')
        >
        >
        >
        >
        >
        > Any help is appreciated.
        >
        >
        >
        >
        >
        > Thanks,
        >
        >
        >
        > King Wilder
      • King Wilder
        John, These returns the same results except the empty cells are replaced with NULLs. Also, I always need to include Position and sometimes Player in the Group
        Message 3 of 3 , Feb 18 9:54 AM
        • 0 Attachment
          John,



          These returns the same results except the empty cells are replaced with
          NULLs.



          Also, I always need to include Position and sometimes Player in the Group By
          clause. I can never run the query without it.



          I'm beginning to think this cannot be done since you can't do an aggregate
          function on a String (varchar) column, Player. Correct?





          King Wilder





          _____

          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          Sent: Sunday, February 18, 2007 6:43 AM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: [SPAM] RE: [SQLQueriesNoCode] Pivot issue...



          Haven't checked this for syntax errors but see if this is close.

          SELECT teeTime,
          MAX(CASE WHEN Position = 1
          THEN Player
          END) AS Player1
          MAX(CASE WHEN Position = 2
          THEN Player
          END) AS Player2
          MAX(CASE WHEN Position = 3
          THEN Player
          END) AS Player3
          MAX(CASE WHEN Position = 4
          THEN Player
          END) AS Player4
          FROM TeeTimesGroups
          GROUP BY TeeTime;

          John Warner

          > -----Original Message-----
          > From: SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%40yahoogroups.com>
          yahoogroups.com
          > [mailto:SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%40yahoogroups.com>
          yahoogroups.com] On Behalf Of King Wilder
          > Sent: Saturday, February 17, 2007 8:58 PM
          > To: SQLQueriesNoCode@ <mailto:SQLQueriesNoCode%40yahoogroups.com>
          yahoogroups.com
          > Subject: [SQLQueriesNoCode] Pivot issue...
          >
          >
          > Hi,
          >
          >
          >
          > I thought I figured this out but I didn't. (I'm going to
          > include a lot of code and results and they are better viewed
          > in Courier New for spacing.)
          >
          >
          >
          > I've tried the pivot sample (in Books Online) using my data
          > and it returns awkward results (see below). I think it has
          > to do with the fact that there is no sum in the statement.
          > Essentially I want to pivot on a row where there is no
          > Aggregate calculation occurring. Is this possible?
          >
          >
          >
          > Here, my raw table results.
          >
          >
          >
          > Player FlightID Position StartTee TeeTime
          >
          > ------------------- --------- ----------- ----------- ---------
          >
          > Brian Watts 1 4 1 7:00 AM
          >
          > Jeff Holdenfield 1 3 1 7:00 AM
          >
          > King Kahanu 1 1 1 7:00 AM
          >
          > Mike Hunt 1 2 1 7:00 AM
          >
          > Al Chang 1 2 1 7:08 AM
          >
          > Doug Furuta 1 3 1 7:08 AM
          >
          > Greg Bull 1 4 1 7:08 AM
          >
          > Mike Delfino 1 1 1 7:08 AM
          >
          > Gene Price 1 1 1 7:16 AM
          >
          >
          >
          > (9 row(s) affected)
          >
          >
          >
          > This is select statement:
          >
          >
          >
          > select teetime,
          >
          > (case position when 1 then Player else '' end) as 'Player1',
          >
          > (case position when 2 then Player else '' end) as 'Player2',
          >
          > (case position when 3 then Player else '' end) as 'Player3',
          >
          > (case position when 4 then Player else '' end) as 'Player4'
          >
          > from TeeTimesGroups
          >
          > where flightid = 1
          >
          > group by teetime, player, position
          >
          > order by teetime, position
          >
          >
          >
          > And here are the results from this query:
          >
          >
          >
          > teetime Player1 Player2 Player3 Player4
          >
          > -------- ------------ ---------- ----------------- ------------
          >
          > 7:00 AM King Kahanu
          >
          > 7:00 AM Mike Hunt
          >
          > 7:00 AM Jeff Holdenfield
          >
          > 7:00 AM Brian Watts
          >
          > 7:08 AM Mike Delfino
          >
          > 7:08 AM Al Chang
          >
          > 7:08 AM Doug Furuta
          >
          > 7:08 AM Greg Bull
          >
          > 7:16 AM Gene Price
          >
          >
          >
          > (9 row(s) affected)
          >
          >
          >
          >
          >
          > As you can see it almost works, but the names for the same
          > time should be on the same row.
          >
          >
          >
          > If you want to test this I've included the sql script to
          > build the table and add some data.
          >
          >
          >
          >
          >
          > if exists (select * from dbo.sysobjects where id =
          > object_id(N'[TeeTimesGroups]') and OBJECTPROPERTY(id,
          > N'IsUserTable') = 1)
          >
          > drop table [TeeTimesGroups]
          >
          > GO
          >
          >
          >
          > CREATE TABLE [TeeTimesGroups] (
          >
          > [TeeTimesGroupID] [int] IDENTITY (1, 1) NOT NULL ,
          >
          > [Player] [varchar] (50) COLLATE
          > SQL_Latin1_General_CP1_CI_AS NULL,
          >
          > [FlightID] [int] NOT NULL ,
          >
          > [Position] [int] NULL ,
          >
          > [StartTee] [int] NULL ,
          >
          > [TeeTime] [varchar] (50) COLLATE
          > SQL_Latin1_General_CP1_CI_AS NULL
          >
          > ) ON [PRIMARY]
          >
          > GO
          >
          >
          >
          >
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('John Williams', 1, 1, 1, '7:00 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Sam Rockwell', 1, 2, 1, '7:00 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Irvin Kirschner', 1, 3, 1, '7:00 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('George Clooney', 1, 4, 1, '7:00 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Mike Ovitz', 1, 1, 1, '7:08 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Jeff Foxworthy', 1, 2, 1, '7:08 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Steve Spielberg', 1, 3, 1, '7:08 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Brad Pitt', 1, 4, 1, '7:08 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Kevin Bacon', 1, 1, 1, '7:16 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Jet Li', 1, 2, 1, '7:16 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('Bruce Lee', 1, 3, 1, '7:16 AM')
          >
          > INSERT INTO TeeTimesGroups (Player, FlightID, Position,
          > StartTee, TeeTime) VALUES ('John Saxon', 1, 4, 1, '7:16 AM')
          >
          >
          >
          >
          >
          > Any help is appreciated.
          >
          >
          >
          >
          >
          > Thanks,
          >
          >
          >
          > King Wilder





          [Non-text portions of this message have been removed]
        Your message has been successfully submitted and would be delivered to recipients shortly.