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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -