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