Share via


Dumping SQL data in pivoted format

Technorati Tags: SQL Server,Tips

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.

image

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:

image

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

and have SQL like below generated automatically,

 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 
  
  
.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; }

 

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

image

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

https://www.sommarskog.se/pivot_sp.sp

https://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.

codeproject