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.?

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.