SQL Pivot Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Pivot Query

Author

Topic

NoNowt
Starting Member

1 Post

Posted - 2014-09-12 : 10:10:44

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

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


Athlete  Mydate	        Session	Mytime
Jerry	  14/04/2009	        200m     00:00:43
Jerry	  14/04/2009	        200m	     00:00:44
Jerry	  14/04/2009	        200m	     00:00:38
Jerry	  14/04/2009	        200m	     00:00:40
Tom	  14/04/2009	        200m	     00:00:45
Tom	  14/04/2009	        200m	     00:00:48
Tom 	  14/04/2009	        200m	     00:00:40
Tom	  14/04/2009	        200m	     00:00:47
Tom	  14/04/2009	        200m	     00:00:48

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

Athlete	Mydate	        Session   Time1	      Time2	      Time3	        Time4	        Time5	     Time6
Jerry	   14/04/2009	   200m	00:00:43    00:00:44	00:00:38	  00:00:40		
Tom	   14/04/2009	   200m	00:00:40    00:00:45	00:00:48	  00:00:40	        00:00:47

Any help most appreciated

Arun Babu N
Starting Member

26 Posts

Posted - 2014-09-15 : 05:44:55

can be done using dynamic pvt qry,

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

Select @ColumnName = isnull(@ColumnName+',','') + times
from (Select distinct 'time'+cast( (row_number()over(partition by Athlete order by Athlete ) ) as varchar) 'times'
from #tt
) as qry1

--Select @ColumnName

Set @DynamicPivotQuery =
'
Select * from
(Select Athlete,Mydate,Mytime,Sessio,''time''+cast( (row_number()over(partition by Athlete order by Athlete ) ) as varchar) as times
from #tt ) as t
pivot
(max(Sessio) for times in ('+@ColumnName+'))as pvt
'
--print @DynamicPivotQuery
EXEC sp_executesql @DynamicPivotQuery

Arun Babu N

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-09-15 : 07:56:10

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

Select @ColumnName = isnull(@ColumnName+',','') + times
from (Select distinct 'time'+cast( (row_number()over(partition by Athlete order by Athlete ) ) as varchar) 'times'
from #tt
) as qry1

--Select @ColumnName

Set @DynamicPivotQuery =
'
Select * from
(Select Athlete,Mydate,Mytime,Session,''time''+cast( (row_number()over(partition by Athlete order by Athlete ) ) as varchar) as times
from #tt ) as t
pivot
(max(MyTime) for times in ('+@ColumnName+'))as pvt
'
--print @DynamicPivotQuery
EXEC sp_executesql @DynamicPivotQuery

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources