Urgent : How to write SQL query??? [Resolved]

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

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