locked
Creating a Crosstab RRS feed

  • Question

  • I have a table called 'tblNormalizedWells'.  This table contains 3 important columns: Well_ID, Depth, Normalized. I want to create a crosstab that will list all wells (Well_ID) across the top (column heading), depths (Depth) along the left (row heading) and the value @ each depth (normalized). 

    There are 882,870 records, 280 Well_IDs and just over 15,000 depths.  I know I need to group by Depth and Well_ID.

    Someone save me! Please!

    Tuesday, December 14, 2010 2:34 AM

Answers

  • Try using dynamic pivot.

    USE tempdb;
    GO
    CREATE TABLE #T (
    Well_ID int NOT NULL,
    Depth int NOT NULL,
    Normalized int NOT NULL
    );
    
    INSERT INTO #T VALUES(1, 10, 101);
    INSERT INTO #T VALUES(2, 12, 102);
    INSERT INTO #T VALUES(3, 13, 103);
    GO
    DECLARE @columns nvarchar(MAX);
    DECLARE @sql nvarchar(MAX);
    
    SET @columns = STUFF (
    (
    SELECT ',' + QUOTENAME(T.Well_ID)
    FROM (SELECT DISTINCT Well_ID FROM #T) AS T
    ORDER BY T.Well_ID
    FOR XML PATH('')
    ), 1, 1, '');
    
    SET @sql = N'SELECT *
    FROM #T PIVOT (MIN(Normalized) FOR Well_ID IN (' + @columns + N')) AS P';
    
    EXEC sp_executesql @sql;
    GO
    DROP TABLE #T;
    GO
    

    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Naomi N Tuesday, December 14, 2010 3:33 AM
    • Marked as answer by KJian_ Tuesday, December 21, 2010 9:12 AM
    Tuesday, December 14, 2010 2:51 AM