sudhirnune (4/9/2013)

---

Thanks all for helping on this: below is procedure which does the Trick

Create procedure USP_TEST

@NAME NVARCHAR(20)

AS

BEGIN

IF object_id(N'tempdb..##TEMP_TEST') is not null

DROP table ##TEMP_TEST;

DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX),

@cols2 AS NVARCHAR(MAX),

@V_NAME AS NVARCHAR(20);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.PAYMENT_TYPE)

FROM TEST_DATA_SRC c

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

SET @Cols2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+PAYMENT_TYPE+'],0) AS ['+PAYMENT_TYPE+']' FROM TEST_DATA_SRC GROUP BY PAYMENT_TYPE FOR XML PATH('')),2,8000)

set @query = 'Select 0 SAL_CACL,0 BONUS_CALC,0 COMM_CALC,name,' + @cols2 + ' into ##TEMP_TEST from

(

select name

, payment

, payment_type

from TEST_DATA_SRC

where name = '''+ @NAME + '''

) x

pivot

(

max(payment)

for payment_type in (' + @cols + ')

) p '

exec sys.sp_executesql @query--,N'@CUR_DATA cursor output',@CUR_DATA output

DECLARE CUR_TEST_FORMULA CURSOR

--Get the Update Query created for executing the same on the Fact Data

FOR

SELECT 'Update ##TEMP_TEST set '+ TGT_PAYMENT_TYPE + ' = ' + PAYMENT_FORMULA +';'

FROM TEST_FORMULA

OPEN CUR_TEST_FORMULA

DECLARE @Q_SQL NVARCHAR(MAX)

FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQL

While @@FETCH_STATUS = 0

BEGIN

print @Q_SQL;

EXECUTE (@Q_SQL);

FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQL

END

CLOSE CUR_TEST_FORMULA

DEALLOCATE CUR_TEST_FORMULA