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?
 
appariCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.