Try this!
Sample Data
Create Table Table1
(
Empcode varchar(10),
SoftwareName varchar(50)
Version varchar(25)
)
Go
Insert Table1 Values('tmp001','Vlc player','Version10.0')
Insert Table1 Values('tmp001','SQL','Version2005')
Insert Table1 Values('tmp002','Access','2000')
Go
Declare @MaxColumn Int, @Column Varchar(Max)
;With MaxCol
As
(
Select Empcode, Count(1) [MaxColumn] from Table1
Group By Empcode
)
Select @MaxColumn = Max(MaxColumn) From MaxCol
;With Cols
As
(
Select 1 as [Col]
Union All
Select [Col] + 1 [Col] From Cols Where Col <@MaxColumn
)
Select @Column = COALESCE(@Column+',','') + '[Software(' + Cast(Col as Varchar) + ')] Varchar(50),[Version('+ Cast(Col as Varchar) +')] Varchar(50)' from Cols
Select @Column = 'Create Table ##Result(Empcode Varchar(50),' + @Column + ')'
Exec(@Column)
Insert ##Result(Empcode)
Select Distinct Empcode from Table1
Declare @Start Int, @Max Int, @Rept Int, @EmpCode Varchar(50)
Select Identity(Int,1,1) RowNo, Row_Number() Over(Partition By Empcode Order by Empcode) [Seq],Empcode
Into #SeqData from Table1
Select @Max = Count(1) From Table1
Select @Start = 1
While(@Start <=@Max)
Begin
Select @Rept=[Seq], @EmpCode =EmpCode from #SeqData Where RowNo = @Start
Select @Column = '
Update H Set H.[Software(' + Cast(@Rept as Varchar) + ')] = T.SoftwareName, H.[Version(' + Cast(@Rept as Varchar) + ')] = T.[Version]
From ##Result H,
(
Select Row_Number() Over(Order by Empcode) RowNo,Row_Number() Over(Partition By Empcode Order by Empcode) [Seq],* from Table1
)as T
Where H.Empcode = T.Empcode
And T.Seq = ' + Cast(@Rept as Varchar)+' And T.EmpCode ='''+ @EmpCode + '''
And T.RowNo = '+ Cast(@Start as Varchar)
Exec(@Column)
Select @Start = @Start + 1
End
Select * from ##Result
Drop Table ##Result
Drop Table #SeqData
Result
Empcode Software(1) Version(1) Software(2) Version(2)
------ --------- -------- ---------- ----------
tmp001 Vlc player Version10.0 SQL Version2005
tmp002 Access 2000 NULL NULL
Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions