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

RE: [SQLQueriesNoCode] Pivot issue...

Expand Messages
  • 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 1 of 3 , Feb 18, 2007
    • 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 2 of 3 , Feb 18, 2007
      • 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.