SQL Server Forums | SQLServerCentral.com / SQL Server 2008 / T-SQL (SS2K8) / SQL /Procedure - for Transpose and Calculation / Latest PostsInstantForum.NET v2.9.0SQL Server Forums | SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 23 Apr 2014 17:41:54 GMT20RE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]Thanks all for helping on this: below is procedure which does the TrickCreate 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 outputDECLARE CUR_TEST_FORMULA CURSOR--Get the Update Query created for executing the same on the Fact DataFOR SELECT 'Update ##TEMP_TEST set '+ TGT_PAYMENT_TYPE + ' = ' + PAYMENT_FORMULA +';' FROM TEST_FORMULAOPEN CUR_TEST_FORMULADECLARE @Q_SQL NVARCHAR(MAX)FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLWhile @@FETCH_STATUS = 0BEGINprint @Q_SQL;EXECUTE (@Q_SQL);FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLENDCLOSE CUR_TEST_FORMULADEALLOCATE CUR_TEST_FORMULA[/quote]What will be consuming the result sets from this stored procedure?Wed, 10 Apr 2013 01:09:50 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxI think you need to stop converting the source data to an excel-like pivot table and do your calculations inline.It will make things much simpler.Unfortunately, you did not provide a test sample, so fo my demonstration I used the one made up by Chris.I changed a small part there - added a PayDate column: I belive you have something like this in your table.[code="sql"]CREATE TABLE #Sampledata ( PayDate smalldatetime, NAME VARCHAR(25), PAYMENT_TYPE VARCHAR(10), PAYMENT DECIMAL(5,2) )[/code]You may embed all your formula variations into a single view:[code="sql"]SELECT DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0) PayMonth, s.NAME, SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END) [NORMAL-SAL], SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END) [NORMAL-COMM], SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) *1.1 [NORMAL-BONUS], SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) [MONTH-SAL], SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [MONTH-COMM], SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [YEARLY-BONUS]FROM #Sampledata s GROUP BY DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0), s.NAME[/code]Than you may just select appropriate columns from the view for appropriate cases.You may chose using dynamic SQL for selecting only required columns from this view, but I'd suggest to move this part to UI/Report module. That's what they are for.Tue, 09 Apr 2013 19:43:05 GMTSergiyRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxThanks all for helping on this: below is procedure which does the TrickCreate 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 outputDECLARE CUR_TEST_FORMULA CURSOR--Get the Update Query created for executing the same on the Fact DataFOR SELECT 'Update ##TEMP_TEST set '+ TGT_PAYMENT_TYPE + ' = ' + PAYMENT_FORMULA +';' FROM TEST_FORMULAOPEN CUR_TEST_FORMULADECLARE @Q_SQL NVARCHAR(MAX)FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLWhile @@FETCH_STATUS = 0BEGINprint @Q_SQL;EXECUTE (@Q_SQL);FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLENDCLOSE CUR_TEST_FORMULADEALLOCATE CUR_TEST_FORMULATue, 09 Apr 2013 13:42:34 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]I will try to Explain you the Scenario and need Suggestion:1. I have te table which is Similar to TEST_SRC for which will tipically has 4000 to 5000 Payment types, along with the County.2. I need has a TEST_FORMULA which will be having Country.3. We need to generate the data which will have the Payment Types as columns.4. We need to now Update Couple of Column based on the Formula defined in the TEST_FORMULA.Please need your help to guidance, which will be help to understand the process / Procedure whcih will help to achive the Above Steps[/quote]Here's what I recommend you do. While you only have [strike]one calculation[/strike] one test case with 3 calculations, write one query to deal with it - similar to or the same as the one I've written for you above. Continue to write queries for new cases until you have five or six test cases with different sets of columns and different calculations, at which point you will also have a representative sample set of data and queries to post here. This approach will provide you with a far more clear picture of the whole process, and will also provide us with sample data and calculations to build a robust solution for you.Tue, 09 Apr 2013 09:04:30 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxI will try to Explain you the Scenario and need Suggestion:1. I have te table which is Similar to TEST_SRC for which will tipically has 4000 to 5000 Payment types, along with the County.2. I need has a TEST_FORMULA which will be having Country.3. We need to generate the data which will have the Payment Types as columns.4. We need to now Update Couple of Column based on the Formula defined in the TEST_FORMULA.Please need your help to guidance, which will be help to understand the process / Procedure whcih will help to achive the Above StepsTue, 09 Apr 2013 08:13:54 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxYes I understand that, but need of time I need to complete it as per the Request.Tue, 09 Apr 2013 07:59:27 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]yes and might increase at any point of time.[/quote]So to summarise, you want to take three or four very simple queries and convert them into a series of statements which are so complicated and convoluted that you need help to write them - at the expense of performance, maintainability and any other criteria which you may choose to distinguish a good query from a less well written one. Are you [i]really sure [/i]you want to do this?Tue, 09 Apr 2013 07:50:04 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxyes and might increase at any point of time.Tue, 09 Apr 2013 07:26:34 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]Because formula are different for Different ScenarioI need to make it dinamic so that based on the Scenario Formula should be Take and Calculated.SCENARIO - TGT_PAYMENT_TYPE - PAYMENT_FORMULANORMAL - SAL - SAL-ADVNORMAL - COMM - ALL_1+ALL_2-ALL_3NORMAL - BONUS - SAL * 1.1MONTH - SAL - SALMONTH - COMM - ALL_1+ALL_2YEARLY - BONUS - SAL+ALL_1+ALL_2[/quote]How many scenarios will you have? I see 3: normal, month and yearly.Tue, 09 Apr 2013 07:21:51 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxBecause formula are different for Different ScenarioI need to make it dinamic so that based on the Scenario Formula should be Take and Calculated.SCENARIO - TGT_PAYMENT_TYPE - PAYMENT_FORMULANORMAL - SAL - SAL-ADVNORMAL - COMM - ALL_1+ALL_2-ALL_3NORMAL - BONUS - SAL * 1.1MONTH - SAL - SALMONTH - COMM - ALL_1+ALL_2YEARLY - BONUS - SAL+ALL_1+ALL_2Tue, 09 Apr 2013 07:18:14 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]Chris. I agree with your Query. My problem is that i need to make it Dynamic.I can not use the Static way of implimenting this.Can you please help me with the Stored proc which can update the data in the Cursor baed on my Formula.I am looking forward for options to update my Procedure whcih i pasted in one of the posts.[/quote]That's straightforward enough. But first, can you explain a little why the query [i]must [/i]be dynamic?Tue, 09 Apr 2013 06:44:05 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxChris. I agree with your Query. My problem is that i need to make it Dynamic.I can not use the Static way of implimenting this.Can you please help me with the Stored proc which can update the data in the Cursor baed on my Formula.I am looking forward for options to update my Procedure whcih i pasted in one of the posts.Tue, 09 Apr 2013 06:41:33 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]there are 3 formula....!SAL = SAL-ADVCOMM = ALL_1+ALL_2-ALL_3BONUS = SAL * 1.1[/quote]Did you test the query I posted above somewhere?Tue, 09 Apr 2013 06:36:53 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxthere are 3 formula....!SAL = SAL-ADVCOMM = ALL_1+ALL_2-ALL_3BONUS = SAL * 1.1Tue, 09 Apr 2013 06:34:30 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]Data in the Test Formula isTGT_PAYMENT_TYPE PAYMENT_FORMULASAL SAL-ADVCOMM ALL_1+ALL_2-ALL_3BONUS SAL * 1.1[/quote]There's only one formula in the table? Are you expecting more? If so, can you show how they might look?Tue, 09 Apr 2013 05:42:48 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxData in the Test Formula isTGT_PAYMENT_TYPE PAYMENT_FORMULASAL SAL-ADVCOMM ALL_1+ALL_2-ALL_3BONUS SAL * 1.1Tue, 09 Apr 2013 05:27:36 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]vinu512 (4/9/2013)[/b][hr]...Nicely Done Chris.[/quote]Thank you, Vinu. Insufficient information yet to determine whether or not it will be useful...Tue, 09 Apr 2013 05:24:09 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxHow many rows does the table TEST_FORMULA contain?Can you post a few rows please?Tue, 09 Apr 2013 05:22:38 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxCODE 1:DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX);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 name, ' + @cols2 + ' from ( select name , payment , payment_type from TEST_DATA_SRC ) x pivot ( max(payment) for payment_type in (' + @cols + ') ) p 'execute(@query)Which helps me to pivot the Data and provide the needed outpout.Below is teh Part of Stored proc whcih will Update the Data based on the Formula from the TEST_FORMULA:Procedure:----------DECLARE CUR_TEST_FORMULA CURSOR--Get the Update Query created for executing the same on the Fact DataFOR SELECT 'Update TEST_TGT set '+ COA_CODE + ' = ' + COA_FORMULA +';' FROM TEST_FORMULAOPEN CUR_TEST_FORMULADECLARE @Q_SQL NVARCHAR(MAX)FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLWhile @@FETCH_STATUS = 0BEGINEXECUTE (@Q_SQL);FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLENDCLOSE CUR_TEST_FORMULADEALLOCATE CUR_TEST_FORMULANeed help to Update the procedure which will Update the Data in the Cursor and insert the detaisl to the TEST_TGTTue, 09 Apr 2013 04:46:06 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]ChrisM@Work (4/9/2013)[/b][hr][quote][b]sudhirnune (4/9/2013)[/b][hr]Yes they has SAL.[/quote]Thanks.[code="sql"]DROP TABLE #SampledataCREATE TABLE #Sampledata (NAME VARCHAR(25), PAYMENT_TYPE VARCHAR(10), PAYMENT DECIMAL(5,2))INSERT INTO #Sampledata (NAME, PAYMENT_TYPE, PAYMENT)SELECT 'SUDHIR', 'SAL', 30.3 UNION ALLSELECT 'SUDHIR', 'ADV', 10.3 UNION ALLSELECT 'SUDHIR', 'ALL_1', 10 UNION ALLSELECT 'SUDHIR', 'ALL_2', 10 UNION ALLSELECT 'SUDHIR', 'ALL_3', 10 UNION ALLSELECT 'MADHAV', 'SAL', 34 UNION ALLSELECT 'MADHAV', 'ALL_1', 24 UNION ALLSELECT 'MADHAV', 'BONUS', 10SELECT s.NAME, [SAL] = s.Payment-x.ADV, [BONUS1] = (s.Payment-x.ADV) * 1.1, -- BONUS from formula [BONUS2] = x.BONUS, -- BONUS from Target Table [COMM] = x.[COMM+] - x.[COMM-]FROM #Sampledata sOUTER APPLY ( SELECT [ADV] = SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END), [COMM+] = SUM(CASE WHEN PAYMENT_TYPE IN ('ALL_1','ALL_2') THEN PAYMENT ELSE 0 END), [COMM-] = SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END), [BONUS] = SUM(CASE WHEN PAYMENT_TYPE = 'BONUS' THEN PAYMENT ELSE 0 END) FROM #Sampledata si WHERE si.NAME = s.NAME AND si.PAYMENT_TYPE <> 'SAL') xWHERE s.PAYMENT_TYPE = 'SAL'ORDER BY s.NAME DESC[/code][/quote]Nicely Done Chris.Tue, 09 Apr 2013 04:31:48 GMTvinu512RE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspx[quote][b]sudhirnune (4/9/2013)[/b][hr]Yes they has SAL.[/quote]Thanks.[code="sql"]DROP TABLE #SampledataCREATE TABLE #Sampledata (NAME VARCHAR(25), PAYMENT_TYPE VARCHAR(10), PAYMENT DECIMAL(5,2))INSERT INTO #Sampledata (NAME, PAYMENT_TYPE, PAYMENT)SELECT 'SUDHIR', 'SAL', 30.3 UNION ALLSELECT 'SUDHIR', 'ADV', 10.3 UNION ALLSELECT 'SUDHIR', 'ALL_1', 10 UNION ALLSELECT 'SUDHIR', 'ALL_2', 10 UNION ALLSELECT 'SUDHIR', 'ALL_3', 10 UNION ALLSELECT 'MADHAV', 'SAL', 34 UNION ALLSELECT 'MADHAV', 'ALL_1', 24 UNION ALLSELECT 'MADHAV', 'BONUS', 10SELECT s.NAME, [SAL] = s.Payment-x.ADV, [BONUS1] = (s.Payment-x.ADV) * 1.1, -- BONUS from formula [BONUS2] = x.BONUS, -- BONUS from Target Table [COMM] = x.[COMM+] - x.[COMM-]FROM #Sampledata sOUTER APPLY ( SELECT [ADV] = SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END), [COMM+] = SUM(CASE WHEN PAYMENT_TYPE IN ('ALL_1','ALL_2') THEN PAYMENT ELSE 0 END), [COMM-] = SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END), [BONUS] = SUM(CASE WHEN PAYMENT_TYPE = 'BONUS' THEN PAYMENT ELSE 0 END) FROM #Sampledata si WHERE si.NAME = s.NAME AND si.PAYMENT_TYPE <> 'SAL') xWHERE s.PAYMENT_TYPE = 'SAL'ORDER BY s.NAME DESC[/code]Tue, 09 Apr 2013 04:03:48 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxYes they has SAL.Tue, 09 Apr 2013 03:25:46 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxI understand that you have multiple payment types, and an employee may have several rows with different payment types. Do ALL employees have at least one row with payment type of 'SAL'?Tue, 09 Apr 2013 01:55:19 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxthe broblem is that I have multiple of such in my ScenarionTue, 09 Apr 2013 01:48:17 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxIs there one payment type which is common to all employees? For instance, 'SAL'? This could be very easy ...Tue, 09 Apr 2013 01:38:03 GMTChrisM@WorkRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxThanks for the above input.Now I am stuck to write a Dynamic SQL which will inter update the Data in the cursor, as per teh data format needed.1. Formula is fetched into a Cursor form the Formula table.2. Decla one more cursore which will fetch the data from the SQL Prepared to fetch the pivot data.2. For loop is used to use the Formula data.3. Need to write a SLQ Query whcih will update the target Columns SAL, BON, COM based on the formula.Tue, 09 Apr 2013 01:26:54 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxThere is no need to hard-code each payment type in a pivot query. [url=http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query]Dynamic SQL[/url] can take care of that.Mon, 08 Apr 2013 20:59:53 GMTMansfieldRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxThanks for the Reply, If you observe my Example, the Payment Type is not Static for all the user and payment Type has nearly 5000 types :(Writing Pivot is a Challenge with out clear understanding.Also by Writing Pivot I am restricting the Payment types.When Ever a new payment come's in I need to Append it to the query.Please check if any such scenarios occured for any of you ???Mon, 08 Apr 2013 20:24:11 GMTsudhirnuneRE: SQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxUse UNPIVOT to turn each PAYMENT_TYPE into its own column. See the following [url=http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.100%29.aspx]link[/url].Then your calculation should be easy :)Mon, 08 Apr 2013 20:19:14 GMTMansfieldSQL /Procedure - for Transpose and Calculationhttp://www.sqlservercentral.com/Forums/Topic1440109-392-1.aspxHi Team,I have a Complex requirement.Source:-------- NAME PAYMENT_TYPE PAYMENTSUDHIR SAL 30.3SUDHIR ADV 10.3SUDHIR ALL_1 10SUDHIR ALL_2 10SUDHIR ALL_3 10MADHAV SAL 34MADHAV ALL_1 24MADHAV BONUS 10Formula:--------TGT_PAYMENT_TYPE PAYMENT_FORMULASAL SAL-ADVCOMM ALL_1+ALL_2-ALL_3BONUS SAL * 1.1Target Table:------------NAME SAL BONUS COMMSUDHIR 20 0 10MADHAV 34 10 24Please need help to code SQL Query / Package which will take the Formula on from the table Formula and apply on Source before pushing it to target.Thanks & Regards,Sudhir Nune.Mon, 08 Apr 2013 20:10:27 GMTsudhirnune