asked on

pivot data

i have this table in SQL:

column1,column2,column3
10,20,30

and i want to build a view that gives this output:

column1, 10, 60, 0.2222
column2, 20, 60, 0.3333
column3, 30, 60, 0.5555

last two column are: total of column1+2+3 and % on total

thanks in advanced

ralmada

Use UNPIVOT.
More reference on UNPIVOT here:
http://www.yafla.com/dennisforbes/UNPIVOT-Normalization-SQL-Server-2000-and-SQL-Server-2005/UNPIVOT-Normalization-SQL-Server-2000-and-SQL-Server-2005.html

;with CTE as (
select Property, Value
(select * from yourtable) o
unpivot (Value for property in (column1, column2, column3)) p
)
select 	Property, 
	Value, 
	(select sum(Value) from CTE) as total, 
	Value / (select sum(Value) from CTE) 
from CTE

Open in new window

Amien90

ASKER

end of #2 there need to be a , right?
getting following error:

incorrect syntax near the keyword 'UNPIVOT'

ralmada

Oops, sorry Missed "FROM" actually

;with CTE as (
select Property, Value
from
(select * from yourtable) o
unpivot (Value for property in (column1, column2, column3)) p
)
select 	Property, 
	Value, 
	(select sum(Value) from CTE) as total, 
	Value / (select sum(Value) from CTE) 
from CTE

Open in new window

Amien90

ASKER

works .. two more issues:

1. last column has all 0 (zeros)
2. what if i want to cluster columns? so what if i want to add column4,5 and 6 .. but the percentage and total should only affect the clustered columns.

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

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

Start Free Trial

Amien90

ASKER

column 1 till 3 is category I

column 4 till 6 is category II

value of total must only be over category for column 1 till 3 ..

when i add column 4 till 6 in the current query .. i will get the total of column 1-6

column1,column2,column3,column4,column5,column6
10,20,30,1,2,3

column1, 10, 60, 0.2222
column2, 20, 60, 0.3333
column3, 30, 60, 0.5555
column4, 1, 6, %
column4, 2, 6, %
column4, 3, 6, %

Amien90

ASKER

typo ... triple column4, should be column4,column5 , column6 at the end..

output now, when i add column4,5,6

column1, 10, 66, 0.2222
column2, 20, 66, 0.3333
column3, 30, 66, 0.5555
column4, 1, 66, ....
column4, 2, 66, ...
column4, 3, 66, ....

ralmada

So maybe something like this:

;with CTE as (
select Property, Value
from
(select * from yourtable) o
unpivot (Value for property in (column1, column2, column3)) p
)
select 	Property, 
	Value, 
	(select sum(Value) from CTE where property in ('column1', 'column2', 'column3')) as total, 
	CASE WHEN property in ('column1', 'column2', 'column3') then Value * 1.00 / (select sum(Value) from CTE where property in ('column1', 'column2', 'column3')) 
	else 0
	end as perc
from CTE

Open in new window

Amien90

ASKER

yes .. but i also want to see the values of column4,5 and 6 ..

i'v changed: unpivot (Value for property in (column1, column2, column3, column4, column5, column6)) p

that gives me this result:

column1, 10, 66, 0.2222
column2, 20, 66, 0.3333
column3, 30, 66, 0.5555
column4, 1, 66, ....
column5, 2, 66, ...
column6, 3, 66, ....

which is almost good because i need

column1, 10, 60, 0.2222
column2, 20, 60, 0.3333
column3, 30, 60, 0.5555
column4, 1, 6, %
column5, 2, 6, %
column6, 3, 6, %

so for column1 till 3 the total column only need to SUM the colums1 till 3
total column on column 4 till 6 needs to be the SUM of column 4 till 6

Amien90

ASKER

thanks for the swift replies btw

Amien90

ASKER

(select sum(Value) from CTE) as total

perhaps add a where clause?

columns can be clustered by taking the first 5 chars of the column name .. ic property

Amien90

ASKER

select  Property, 
        Value, 
        (select sum(Value) from CTE WHERE LEFT(t.property,5) = LEFT(property,5)) as total, 
        Value * 1.00 / (select sum(Value) from CTE WHERE LEFT(t.property,5) = LEFT(property,5)) as percentage
from CTE t

Open in new window

ralmada

What about this?

;with CTE as (
select Property, Value, case when Property in ('column1', 'column2', 'column3') then 1 else 0 end as part
from
(select * from yourtable) o
unpivot (Value for property in (column1, column2, column3, column4, column5, column6)) p
)
select 	Property, 
	Value, 
	(select sum(Value) over(partition by part) from CTE) as total,
	Value * 1.00 / (select sum(Value) over(partition by part) from CTE) as percentage
 from CTE

Open in new window

ralmada

Sorry I meant like this:

;with CTE as (
select Property, Value, case when property in ('column1', 'column2', 'column3') then 1 else 0 end as part
from
(select * from unpivo) o
unpivot (Value for property in (column1, column2, column3, column4, column5, column6)) p
)
select Property, Value, sum(Value) over (partition by part) as total, value * 1.0 / sum(Value) over (partition by part) as percentage from CTE
order by Property

Open in new window