asked on

Convert summed columns to Rows

Hi I have a table which returns data as per the following - eg

Select * from Mytable

Name ID Ref1 Ref2 Ref3
ABCD 1 10 12 14
ABCD 2 0 4 44
XYZ 7 34 6 21
XYZ 9 8 7 5

However, what I want to achieve is the following result set:
Name Ref Total_Amount
ABCD Ref1 10
ABCD Ref2 16
ABCD Ref3 58
XYZ Ref1 42
XYZ Ref2 13
XYZ Ref3 26

Regards

PHIL Sawyer

ASKER

Hi
Forgot to mention that I do not want to use the Union method as there are so many Names and Ref in the real table.

Regards

slightwv (䄆 Netminder)

Try unpivot:

select *
from mytable unpivot(testdate for reftype in (
	ref1 as 'Ref1',
	ref2 as 'Ref2',
	ref3 as 'Ref3'
	)
	)
/

Open in new window

slightwv (䄆 Netminder)

oops forgot the SUM. Give me a minute.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

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

Start Free Trial

PHIL Sawyer

ASKER

Thanks - works a treat.

Regards

PHIL Sawyer

ASKER

Good work - many thanks