SQL

I have a stored procedure  (SP1) that return the following table.
for example ,   exec SP1 32111
Risk      Amount
1      NULL
2      0.00
3      15282.95
4      33159.88
5      0.00
6      0.00

I will like to turn the Amount into Percent (by totalizing the Amount columns)
Then I will like to insert the result  as a record into a table in this format.  How do I do that
SPParm |  1       | 2       | 3         | 4         | 5         | 6        |
-----------------------------------------------------------------------------
32111        0           0         31.55     68.45     0            0


I am runing SQL Server 2003
tommym121Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
appariConnect With a Mentor Commented:
try this
declare @tab table(Risk  int,    Amount numeric(10,2))

insert into @tab
Select 1,      NULL
union select 2,      0.00
union select 3,      0 --15282.95
union select 4,      0 --33159.88
union select 5,      0.00
union select 6,      0.00

;
SELECT SPPARAM, 
sum(case when Risk=1 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else 0 end * 100.0 [1], 
sum(case when Risk=2 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [2], 
sum(case when Risk=3 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [3], 
sum(case when Risk=4 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [4], 
sum(case when Risk=5 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [5], 
sum(case when Risk=6 then AMOUNT else 0 end )/case when sum(Amount)=0 then 1 else 0 end * 100.0 [6]
FROM
(SELECT 32111 SPPARAM,Risk, isnull(Amount,0) AMOUNT
    FROM @tab) AS SourceTable
Group by SPPARAM

Open in new window

0
 
appariCommented:
can you post the existing sql from the stored procedure?
You can do it using pivot operator, check the following for an idea
declare @tab table(Risk  int,    Amount numeric(10,2))

insert into @tab
Select 1,      NULL
union select 2,      0.00
union select 3,      15282.95
union select 4,      33159.88
union select 5,      0.00
union select 6,      0.00

;
SELECT 32111 SPPARAM,[1], [2], [3], [4], [5],[6]
FROM
(SELECT Risk, isnull(Amount,0)/ Total * 100.0 TOT
    FROM @tab, (Select Sum(Amount) Total from @tab) A) AS SourceTable
PIVOT
(
sum(TOT)
FOR RISK IN ([1], [2], [3], [4], [5],[6])
) AS PivotTable

Open in new window

0
 
tommym121Author Commented:
Is there anyway to do  without pivot.  SQL server 2003 does not support PIVOT
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
appariCommented:
then try like this
declare @tab table(Risk  int,    Amount numeric(10,2))

insert into @tab
Select 1,      NULL
union select 2,      0.00
union select 3,      15282.95
union select 4,      33159.88
union select 5,      0.00
union select 6,      0.00

;
SELECT SPPARAM, 
sum(case when Risk=1 then AMOUNT else 0 end )/sum(Amount) * 100.0 [1], 
sum(case when Risk=2 then AMOUNT else 0 end )/sum(Amount) * 100.0 [2], 
sum(case when Risk=3 then AMOUNT else 0 end )/sum(Amount) * 100.0 [3], 
sum(case when Risk=4 then AMOUNT else 0 end )/sum(Amount) * 100.0 [4], 
sum(case when Risk=5 then AMOUNT else 0 end )/sum(Amount) * 100.0 [5], 
sum(case when Risk=6 then AMOUNT else 0 end )/sum(Amount) * 100.0 [6]
FROM
(SELECT 32111 SPPARAM,Risk, isnull(Amount,0) AMOUNT
    FROM @tab) AS SourceTable
Group by SPPARAM

Open in new window

0
 
tommym121Author Commented:
appari,

Thanks.  How do I avoid 'Divide by zero'  if my Dataset like this
Risk      Amount
1      NULL
2      0.00
3      0.00
4      NULL
5      0.00
6      0.00

I will like to turn the Amount into Percent (by totalizing the Amount columns)
Then I will like to insert the result  as a record into a table in this format.  How do I do that
SPParm |  1       | 2       | 3         | 4         | 5         | 6        |
-----------------------------------------------------------------------------
32111        0           0           0            0            0            0
0
 
tommym121Author Commented:
Thanks.  I find a mistake that  cause a divison by zero.   I change

/ case when sum(Amount)=0 then 1 else  0 end * 100.0 [1],
into
/ case when sum(Amount)=0 then 1 else  sum(Amount) end * 100.0 [1],


            sum(case when Risk=1 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else  sum(Amount) end * 100.0 [1],
            sum(case when Risk=2 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else  sum(Amount)  end * 100.0 [2],
            sum(case when Risk=3 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else  sum(Amount)  end * 100.0 [3],
            sum(case when Risk=4 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else  sum(Amount)  end * 100.0 [4],
            sum(case when Risk=5 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else  sum(Amount)  end * 100.0 [5],
            sum(case when Risk=6 then AMOUNT else 0 end )/ case when sum(Amount)=0 then 1 else  sum(Amount)  end * 100.0 [6]
0
 
tommym121Author Commented:
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.