pivot rows and columns in the same query

  • 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.

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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