You shouldn't store the information in either format as neither way is normalized.
Assuming your current data is something like this:
CREATE TABLE TestData(
Expenses varchar(20),
aaaaaa decimal(18, 4),
bbbbbb decimal(18, 4),
cccccc decimal(18, 4));
INSERT INTO TestData VALUES
('Expense1',100.00, 200.00, 300.00),
('Expense2',25.00 , 35.00 , 45.00),
('Expense3',225.00, 500.25, 35.00);
You should store your data unpivoted. The following method is explained in here:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
SELECT ID ,
Expenses,
VALUE
FROM TestData
CROSS APPLY (VALUES( 'aaaaaa', aaaaaa),
( 'bbbbbb', bbbbbb),
( 'cccccc', cccccc))x(ID, VALUE);
But if you want to use it for reporting, here's the output you requested using CROSS TABS over the unpivoted data as explained in the following article:
http://www.sqlservercentral.com/articles/T-SQL/63681/
SELECT ID ,
SUM( CASE WHEN Expenses = 'Expense1' THEN VALUE ELSE 0 END) AS [Expense1],
SUM( CASE WHEN Expenses = 'Expense2' THEN VALUE ELSE 0 END) AS [Expense2],
SUM( CASE WHEN Expenses = 'Expense3' THEN VALUE ELSE 0 END) AS [Expense3]
FROM TestData
CROSS APPLY (VALUES( 'aaaaaa', aaaaaa),
( 'bbbbbb', bbbbbb),
( 'cccccc', cccccc))x(ID, VALUE)
GROUP BY ID;
To make it dynamic, you just need to identify the static and dynamic parts to generate the correct code. I concatenate the values using the following method:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
DECLARE @SQL nvarchar(max),
@Columns nvarchar(max),
@Expenses nvarchar(max);
SELECT @Columns = STUFF((SELECT ',( ''' + COLUMN_NAME + ''', ' + COLUMN_NAME + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TestData'
AND ORDINAL_POSITION > 1 --Don't include Expenses column
FOR XML PATH('')), 1, 1, 'VALUES');
SELECT @Expenses = CAST((SELECT ',SUM( CASE WHEN Expenses = ' + QUOTENAME(Expenses, '''') + ' THEN VALUE ELSE 0 END) AS ' + QUOTENAME(Expenses) + ''
FROM TestData
FOR XML PATH('')) AS nvarchar(max))
SET @SQL = '
SELECT ID ' + @Expenses + '
FROM TestData
CROSS APPLY ('+ @Columns +')x(ID, VALUE)
GROUP BY ID;'
PRINT @SQL;
EXEC sp_executesql @SQL;
Usually I format my dynamic code, but I'm in a hurry right now.
I hope this is clear enough for you, but you can ask any questions that you have.