March 26, 2015 at 4:41 pm
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
March 26, 2015 at 7:12 pm
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.
March 26, 2015 at 8:13 pm
This is good stuff! Thanks Luis for sharing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply