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;
}
.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:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[util_PivotAllColumns]
@FromSpecifier NVARCHAR(MAX),
@AfterFromClause NVARCHAR(MAX) = NULL,
@ColumnList NVARCHAR(MAX) = '*',
@PrintSelectStatement BIT = 0
AS
BEGIN
SET NOCOUNT ON
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)
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO @ColumnName
DECLARE @ColOrder INT = 0
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @ColumnSpecifier NVARCHAR(2000) = N''
IF @ColOrder > 0
SET @ColumnSpecifier = N' UNION ALL SELECT '
ELSE SET @ColumnSpecifier = N'SELECT '
SET @ColumnSpecifier =
@ColumnSpecifier + CONVERT(NVARCHAR(2000),@ColOrder) + N' AS ColSeq, '
SET @ColumnSpecifier = @ColumnSpecifier + N'''' + @ColumnName +
N''' AS ColName, '
SET @ColumnSpecifier = @ColumnSpecifier + N'CONVERT(NVARCHAR(MAX),
[' + @ColumnName + '])
AS ColValue'
SET @ColumnSpecifier = @ColumnSpecifier + N' FROM ' + @FromSpecifier +
' ' + COALESCE(@AfterFromClause,'')
SET @SQLCmd = COALESCE(@SQLCmd,N'') + CHAR(13) + CHAR(10) + @ColumnSpecifier
SET @ColOrder = @ColOrder + 1
FETCH NEXT FROM ColumnCursor INTO @ColumnName
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
END
ELSE BEGIN
PRINT 'Sorry, I dont parse column lists yet'
END
SELECT @SQLCmd
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:
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.