Pivot query

[Vb.Netter]

I have a simple sql table with four columns The data types are

Athlete nvarchar(30)
Mydate  date
Mytime  varchar(8)
Session nvarchar(40)

Sample Data
**Athlete  Mydate     Mytime   Session**
  Jerry   14/07/2014  00:00:32 200M 
  Jerry   14/07/2014  00:00:31 200M
  Jerry   16/07/2014  00:00:59 400M
  Jerry   16/07/2014  00:00:57 400M
  Jerry   16/07/2014  00:00:58 400M

  Now what i want is to create a query that outputs as below

**Athlete  Mydate     Mytime1   Mytime2  Mytime3  Mytime4  Mytime5  Mytime6  Mytime7  Mytime8  Mytime9  Mytime10  Session**
  Jerry   14/07/2014  00:00:32  00:00:31                                                                          200M    
  Jerry   16/07/2014  00:00:59  00:00:57 00:00:58                                                                 400M    

  is this possible.?

[adam_k]

Assuming that you only need Mytime1 to Mytime10 (10 pivoted columns) only:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

If it's a dynamic pivot, one where Mytime can be anything from 1 column up to whatever, then you need dynamic SQL. Let me know and I'll provide.

[Vb.Netter]

Hi
That topic no longer exists,but it would be most helpful if you could provide
a solution using dynamic sql as it would very useful to not have a restriction on number of columns for mytime.

Thank you

[Borzoi]

Adam's link broke when posting. Try this:

http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

[adam_k]

This guy talks about dynamic columns in pivots:

https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/