Dumping SQL Data in Pivoted Format

If you’re like me and spend a lot of time in SQL Query Analyzer, querying data directly, you may find the column display format tedious for tables with lots of columns or where you are only working with a couple of entries in the table anyways.

Consider the following data that I was just trying to dump out as part of another blog post related to my doctoral research on automated software.

That’s not too bad, but I only have 5 columns.  What if I have a lot more as in select * from HumanResources.Employee Where EmployeeId = 1 using Adventureworks database:

Those are just the first few columns. Unless you have a 30 inch wide screen with 3000 pixels across, you still won’t be able to see everything across the width of the screen without scrolling.

Wouldn’t it be nice if we could just do something like exec util_PivotAllColumns.

EXEC  [dbo].[util_PivotAllColumns]
            @FromSpecifier = N'Person.Contact',
            @AfterFromClause = 'WHERE ContactId = 1',
            @ColumnList = '*',
            @PrintSelectStatement = 1

SELECT 0 AS ColSeq, 'EmployeeID' AS ColName, CONVERT(NVARCHAR(MAX),
   [EmployeeID]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 1 AS ColSeq, 'NationalIDNumber' AS ColName,
    CONVERT(NVARCHAR(MAX),[NationalIDNumber]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 2 AS ColSeq, 'ContactID' AS ColName, CONVERT(NVARCHAR(MAX),
    [ContactID]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 3 AS ColSeq, 'LoginID' AS ColName, CONVERT(NVARCHAR(MAX),
    [LoginID]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 4 AS ColSeq, 'ManagerID' AS ColName, CONVERT(NVARCHAR(MAX),
    [ManagerID]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 5 AS ColSeq, 'Title' AS ColName, CONVERT(NVARCHAR(MAX),
    [Title]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 6 AS ColSeq, 'BirthDate' AS ColName, CONVERT(NVARCHAR(MAX),
    [BirthDate]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 7 AS ColSeq, 'MaritalStatus' AS ColName, CONVERT(NVARCHAR(MAX),
    [MaritalStatus]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 8 AS ColSeq, 'Gender' AS ColName, CONVERT(NVARCHAR(MAX),
    [Gender]) AS ColValue FR
OM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 9 AS ColSeq, 'HireDate' AS ColName, CONVERT(NVARCHAR(MAX),
    [HireDate]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 10 AS ColSeq, 'SalariedFlag' AS ColName, CONVERT(NVARCHAR(MAX),
    [SalariedFlag]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 11 AS ColSeq, 'VacationHours' AS ColName, CONVERT(NVARCHAR(MAX),
    [VacationHours]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 12 AS ColSeq, 'SickLeaveHours' AS ColName, CONVERT(NVARCHAR(MAX),
    [SickLeaveHours]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 13 AS ColSeq, 'CurrentFlag' AS ColName, CONVERT(NVARCHAR(MAX),
    [CurrentFlag]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 14 AS ColSeq, 'rowguid' AS ColName, CONVERT(NVARCHAR(MAX),
    [rowguid]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1

 UNION ALL SELECT 15 AS ColSeq, 'ModifiedDate' AS ColName, CONVERT(NVARCHAR(MAX),
    [ModifiedDate]) AS ColValue 
FROM HumanResources.Employee WHERE EmployeeId = 1
 UNION ALL SELECT 1 AS ColSeq, 'NameStyle' AS ColName, CONVERT(NVARCHAR(MAX),
    [NameStyle]) AS ColValue FROM Person.Contact

style type="text/css">
.csharpcode, .csharpcode pre
{
	font-size: small;
	color: black;
	font-family: consolas, "Courier New", courier, monospace;
	background-color: #ffffff;
	/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt 
{
	background-color: #f4f4f4;
	width: 100%;
	margin: 0em;
}
.csharpcode .lnum { color: #606060; }</style>

So, we could see our data like this directly from Query Analyzer.

Enter the util_PivotAllColumns stored proc:

/****** Object:  StoredProcedure [dbo].[util_PivotAllColumns]    
Script Date: 06/16/2009 16:43:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Bob Leithiser
-- Create date: 6/16/2009
-- Description:    Pivots all columns from a table and selects primary key value if 
-- specified as a parameter
-- WARNING: This isn't safe from SQL Injection, not to be used for production, just
-- a testing/dumping tool.
-- Uses sys.columns view from current database where the stored proc is located, 
-- so you must create in every
-- database that you want to use this in.
-- =============================================
CREATE PROCEDURE [dbo].[util_PivotAllColumns]
  @FromSpecifier NVARCHAR(MAX),  
  @AfterFromClause NVARCHAR(MAX) = NULL, -- typically the where clause, but make it 
                                         -- flexible for group by, etc.
  -- Downside is that this means user must supply the WHERE keyword rather than
  -- just the criteria.
  @ColumnList NVARCHAR(MAX) = '*',
  @PrintSelectStatement BIT = 0
AS 
BEGIN
/* Usage Example:
EXEC [dbo].[util_PivotAllColumns]
     @FromSpecifier = N'Person.Contact',
     @AfterFromClause = 'WHERE ContactId = 1',
     @ColumnList = '*',
     @PrintSelectStatement = 1
-- You can omit the optional parameters and for a quick table dump just do:
EXEC [dbo].[util_PivotAllColumns] 'Tablename'
*/ 

  -- TODO: Validate input parameters and add try/catch exception handling
  -- TODO: Support multiple tables in the FROM clause
  -- TODO: Add parsing to support column list
  
    SET NOCOUNT ON
    -- Assuming just a single table at this point, not parsing for multiple
    -- DECLARE @TableName SYSNAME =  OBJECT_NAME(OBJECT_ID(@FromSpecifier))
    -- DECLARE @SchemaName SYSNAME = OBJECT_SCHEMA_NAME(OBJECT_ID(@FromSpecifier))
    -- Get the column list if not provided
    IF COALESCE(@ColumnList,'*') = '*'
    BEGIN
        DECLARE @SQLCmd NVARCHAR(MAX)
        DECLARE @ColumnName SYSNAME
        
        DECLARE ColumnCursor CURSOR FOR 
        SELECT [name]
            FROM sys.columns 
            WHERE object_id = OBJECT_ID(@FromSpecifier) -- Need to add parsing for
                                                        -- multiple tables, joins, etc.
        OPEN ColumnCursor
        FETCH NEXT FROM ColumnCursor INTO @ColumnName
        DECLARE @ColOrder INT = 0
        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @ColumnSpecifier NVARCHAR(2000) = N''
            
            -- Once past first column, tack on UNION ALL
            IF @ColOrder > 0 
                SET @ColumnSpecifier = N' UNION ALL SELECT '
                ELSE SET @ColumnSpecifier = N'SELECT '
                
            -- Add the column SEQuencer
            SET @ColumnSpecifier = 
                @ColumnSpecifier + CONVERT(NVARCHAR(2000),@ColOrder) + N' AS ColSeq, '
                -- 2000 columns ought to be enougn
            
            -- Add the column NAME
            SET @ColumnSpecifier = @ColumnSpecifier + N'''' + @ColumnName + 
                N''' AS ColName, '            
            
            -- Add the column VALUE - Have to convert to same type - use nvarchar - so all
            -- the unions get along
            SET @ColumnSpecifier = @ColumnSpecifier + N'CONVERT(NVARCHAR(MAX),
               [' + @ColumnName    + ']) 
AS ColValue'
            -- Add the FROM clause and AFTER From Clause 
            -- (typically the WHERE clause - must include WHERE)
            SET @ColumnSpecifier = @ColumnSpecifier + N' FROM ' + @FromSpecifier + 
               ' ' + COALESCE(@AfterFromClause,'')

            -- Add the column specifier to the SQL Command String and toss
            -- in c/r l/f to make more source query
            -- readable    
            SET @SQLCmd = COALESCE(@SQLCmd,N'') + CHAR(13) + CHAR(10) + @ColumnSpecifier
            
            -- Increment the column sequencer
            SET @ColOrder = @ColOrder + 1
            FETCH NEXT FROM ColumnCursor INTO @ColumnName
        END
        CLOSE ColumnCursor
        DEALLOCATE ColumnCursor
    END
    ELSE BEGIN
    -- Parse the column list and do inline replacements
        PRINT 'Sorry, I dont parse column lists yet'
    END

    SELECT @SQLCmd    -- For debugging
    IF @PrintSelectStatement = 1
    BEGIN
        PRINT @SQLCmd
    END
    EXEC sp_ExecuteSQL @stmt = @SQLCmd
END
GO

I asked around a little and found out about some neat dynamic SQL generators for pivoting, see:

• http://www.sommarskog.se/pivot_sp.sp
• http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

but these were overkill for what I needed, plus I wanted something quick and easy to use without having to think about how to summarize the data.