Complicate SQL Amount split by percentage in same row transpose (Pivot)?

Question

I am struggling to split total amount field into percentage in the same row and then update the last column with Amount type for which the percentage is applied.

Example data

        Total Amount |  UF%  | UFI%     |RA%    |RL%    |NP%    | AmountType

            100      |0.00   |20        |9.15   |0.75   |70.01  
            1520.23  |64.4   |19.1      |15.5   |0.25   |0.75   
            158520.03|13.25  |35        |2.25   |19.28  |30.22

I have to get percentage of total amount column and then transpose insert them as additional rows in the same table and upate the last column what type of amount it is.

For example for 1st row I can get 5 new rows

Total Amount    Amount type
 0              UF%
 20             UFI%
 9.15           RA%
 0.75           RL%
 70.01          NP%

I am one step at a time to I have created 5 new columns to calculate the percentage as TotalAmount UF%, TotalAmount UFI%, TotalAmountRA% and so on… Selec t [Total Amount]* UF% as [TotalAmount UF%] … and so on.

I am stuck here shall I use Pivot/unpivot? Or case ? Or is it any other easier way to use row over partition by ?

Please suggest.

in the provided data sample there are 7 headers and 6 columns. the 7th column what should contain? — Paolo, Sep 01 '15 at 15:47
@Paolo I think is just a reference each column is a `AmountType` as you can see in the OP expect result — Juan Carlos Oropeza, Sep 01 '15 at 15:48
BTW. You want `TotalAmount` * `AmountType%`? Because having an example with TotalAmount =100 can be confusing. Dont know if you want `AmountType%` — Juan Carlos Oropeza, Sep 01 '15 at 15:53
7TH Column (AmountType%) is blank, it need to be update with the amount type UF, UFI... and so on — PD SQL, Sep 01 '15 at 16:20
possible duplicate of [SQL Server : Columns to Rows](http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — Tab Alleman, Sep 01 '15 at 17:11
If my answer was helpful, please vote up and/or mark as accepted, thx! — Shnugo, Sep 10 '15 at 13:27

Shnugo · Accepted Answer · 2015-09-02T11:23:33.230

1

this should work for you. Just copy this into an empty query window and execute. Adapt to your needs...

EDIT: Calculate percentages...

declare @amounts table (TotalAmount decimal(8,2),[UF%] decimal(4,2), [UFI%] decimal(4,2)
                                                ,[RA%] decimal(4,2),[RL%] decimal(4,2)
                                                ,[NP%] decimal(4,2));
insert into @amounts values
 (100,0.00,20,9.15,0.75,70.01)  
,(1520.23,64.4,19.1,15.5,0.25,0.75)   
,(158520.03,13.25,35,2.25,19.28,30.22);

select up.TotalAmount
      ,up.Percentag
      ,(up.TotalAmount/100)*up.Percentag AS AmountPercentage
      ,up.Amount AS AmountType
from
(
    select *
    from @amounts  
) AS tbl
unpivot
(
    Percentag FOR Amount IN([UF%],[UFI%],[RA%],[RL%],[NP%])
) AS up

edited Sep 02 '15 at 11:23

answered Sep 01 '15 at 20:55

Shnugo

62,351
7
42
92

Thanks it helps to some extent but before we unpivot I need to calculate the percentage by multiplying Total Amount with each Percentage.. Total Amount X UF%..TotalAmount X RA% ..and so on and then unpivot the value of the percentage. – PD SQL Sep 02 '15 at 08:43
@PuneetD, Isn't it much easier to do the calculation after the PIVOT? I edited my answer, just check my solution... And please vote up and/or mark as accepted if helpful, thx – Shnugo Sep 02 '15 at 11:24
Yeah that makes sense to me, Let me first pivot the the % column and then calculate the percentage of amount. – PD SQL Sep 02 '15 at 14:14
Hi Shungo, I am trying to accept that but as I new member until I earn 15 points I can't accept. Do you know any other wayt ot accept it as ans? – PD SQL Sep 14 '15 at 10:02
@PuneetD, Hi thx for this! Just ask some good questions and you'll get your points soon. I just upvoted the one above... It is not that difficult to gain 15 points. Just remember me and come back when you've got the points :-) – Shnugo Sep 14 '15 at 10:05
@PuneetD, oh wait, you've reached the points already ;-) (I've pushed you a little bit :-) ) – Shnugo Sep 14 '15 at 10:06
Done now. Thanks for your help. – PD SQL Sep 14 '15 at 10:13

Complicate SQL Amount split by percentage in same row transpose (Pivot)?

1 Answers1