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

Posted by Somasundaram under Sql Server on 6/16/2012 | Points: 10 | Views : 1165 | Status : [Member] | Replies : 2
Hi.,

I have DB structure as follows,

Empcode,SoftwareName,Version --------Column Names
tmp001,Vlc player,Version10.0
tmp001,SQL,Version2005

I need the Output as follows,

Empcode,Software(1),Version(1),Software(2),Version(2) ----Column Names(dynamically Creating according to number of records in DB for each empcode)
tmp001,vlc Player,Version10.0,SQL,Version 2005 -----Record of particular employee

If anybody have any idea pls post it..

Thanks in Advance,

Regards,
Somasundaram




Responses

Posted by: Pandians on: 6/16/2012 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
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

Somasundaram, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Somasundaram on: 6/17/2012 [Member] Starter | Points: 25

Up
0
Down
hi pandians,

Thank you very much...


Regards,
Somasundaram

Somasundaram, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response