No where i found general logic if the rows/column are unknown, then i written the following and it works
Example :
Table 1 : PayEarnings
EarnCode EarnName
-------- --------------------------------------------------
001 BasicSalary
002 Overtime
003 OtherAllowances A
Table 2: PayDesignation
DesigCode DesigName
--------- --------------------------
01 CEO
02 Directors
03 Accountant
Table 3: PayBudget [Transaction table]
DesigCode EarnCode Yearly
--------- -------- ---------------------------------------
01 001 60
01 002 70
01 003 80
02 001 50
02 002 60
02 003 70
03 001 40
03 002 50
03 003 60
Store Procedure : user store procedure TransPose
Create Procedure uspTransPose
as
Declare @STR nvarchar(4000)
Declare @EarnName nvarchar(50)
DECLARE @NL AS CHAR(2)
set @NL= CHAR(13) + CHAR(10) ---- New line
--- Creating transpose table
Set @STR=N'Create table #tmp (DesigName Nvarchar(50)'
DECLARE TranposeCur CURSOR FAST_FORWARD FOR select EarnName from PayEarnings
OPEN TranposeCur
FETCH NEXT FROM TranposeCur INTO @EarnName
WHILE @@FETCH_STATUS = 0
BEGIN
---- Adding columns in runtime
Set @STR=@Str +',['+@EarnName+'] Numeric(18)'
FETCH NEXT FROM TranposeCur INTO @EarnName
End
Close TranposeCur
DeAllocate TranposeCur
Set @STR=@Str+')'
---- End of transpose table creation
--- Inserting the designation records
Set @STR=@Str +@NL+'Insert into #tmp (DesigName) select DesigName from PayDesignation'
--- to minimise the lenght of @STR, actual query with join is now stored in #tmp2 table
Select Designame,EarnName,Yearly
into #tmp2
From PayBudget PB
Left Join PayEarnings PE on PE.EarnCode=PB.EarnCode
Left Join PayDesignation PD on PD.DesigCode=PB.DesigCode
DECLARE TranposeCur CURSOR FAST_FORWARD FOR select EarnName from PayEarnings
OPEN TranposeCur
FETCH NEXT FROM TranposeCur INTO @EarnName
WHILE @@FETCH_STATUS = 0
BEGIN
--- update statement
Set @STR=@str+@NL+'Update #tmp set ['+@EarnName+']=(Select isnull(Yearly,0) From #tmp2 Where DesigName=#tmp.DesigName and EarnName='''+@EarnName+''')'
FETCH NEXT FROM TranposeCur INTO @EarnName
End
Close TranposeCur
DeAllocate TranposeCur
--- to get the output
Set @STR=@str+@NL+'Select * from #tmp'
-- to get actual query
select @STR
--- to get output
EXECUTE sp_executesql @STR
Output :
DesigName BasicSalary Overtime OtherAllowances A
-------------------- --------------- ---------- -----------------
CEO 60 70 80
Directors 50 60 70
Accountant 40 50 60
Thanks
R.Arul Murugan
+91 98403 40969
+971 50 164 7438
16/07/2010