以透视格式转储 SQL 数据
如何以透视格式转储 SQL 数据。
如果你像我一样，经常在 SQL 查询分析器中直接查询数据，你可能会发现对于包含大量列的表，或者你只是处理表中的少量条目时，列显示格式非常繁琐。
考虑一下我正在尝试作为另一篇关于我博士研究自动化软件的博客文章的一部分而转储的以下数据。
这还不错，但我只有 5 列。如果我像使用 Adventureworks 数据库中的 select * from HumanResources.Employee Where EmployeeId = 1 这样有更多列呢？
这些只是前几列。除非你有一个 30 英寸宽、跨度 3000 像素的屏幕，否则你仍然无法在不滚动的情况下看到屏幕上的所有内容。
如果我们能像 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>
这样，我们可以直接从查询分析器中看到我们的数据，就像这样。
介绍 util_PivotAllColumns 存储过程
/****** 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
我问了一些人，发现了一些用于透视的不错的动态 SQL 生成器，请参阅
但这些对于我的需求来说过于复杂，而且我想要一个快速且易于使用的东西，而无需考虑如何汇总数据。