asked on

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

appari

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

tommym121

ASKER

Is there anyway to do without pivot. SQL server 2003 does not support PIVOT

appari

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

tommym121

ASKER

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

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tommym121

ASKER

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]

tommym121

ASKER

Thank you very much