代码之家 › 专栏 › 技术社区 › Aarion

SQL Server数据透视/Unpivot计数

unpivot pivot count sql-server sql

Aarion · 技术社区 · 5 年前

我需要一些关于UnPivot/Pivot/Cross-Apply类型操作的帮助。

背景:数据是每个数据库中每个表的行计数,每天两次:上午和晚上。

样本数据:

CREATE TABLE [dbo].[DataCount](
    [DatabaseName] [varchar](255) NULL,
    [TableName] [varchar](255) NULL,
    [RowCount] [bigint] NULL,
    [Date] [date] NULL,
    [DateForAnalysis] [datetime] NULL,
    [Runtime] [varchar](50) NULL
) 



INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableA', 10, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 09:00:00.000' AS DateTime), N'Morning')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableB', 10, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 09:00:00.000' AS DateTime), N'Morning')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableC', 10, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 09:00:00.000' AS DateTime), N'Morning')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableA', 15, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 18:00:00.000' AS DateTime), N'Evening')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableB', 15, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 18:00:00.000' AS DateTime), N'Evening')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableB', 15, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 18:00:00.000' AS DateTime), N'Evening')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableA', 20, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 09:00:00.000' AS DateTime), N'Morning')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableB', 20, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 09:00:00.000' AS DateTime), N'Morning')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableC', 20, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 09:00:00.000' AS DateTime), N'Morning')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableA', 25, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 18:00:00.000' AS DateTime), N'Evening')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableB', 25, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 18:00:00.000' AS DateTime), N'Evening')
GO
INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) VALUES (N'DatabaseA', N'TableC', 25, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 18:00:00.000' AS DateTime), N'Evening')
GO

所需的示例数据输出如下图所示,但可以就如何更好地显示数据提出建议。

0 回复 | 直到 5 年前

Serkan Arslan 5 年前

你需要一个动态轴心。

DECLARE @ColNames NVARCHAR(MAX) = ''

SELECT @ColNames = @ColNames + ',' + DateAndRuntime
FROM (SELECT DISTINCT QUOTENAME( CONCAT([Date] , ' ',   [Runtime] )) DateAndRuntime FROM [dbo].[DataCount] ) T

SELECT @ColNames = STUFF( @ColNames ,1,1,'')

DECLARE @PilotSql NVARCHAR(MAX) = 'SELECT * FROM (
    SELECT [DatabaseName], [TableName], [RowCount], CONCAT([Date] , '' '', [Runtime] ) AS [DateAndRuntime] FROM [dbo].[DataCount] ) 
AS  SRC
PIVOT (SUM([RowCount]) FOR [DateAndRuntime] IN ('+@ColNames+')) PVT '

EXECUTE sp_executesql @PilotSql

结果:

DatabaseName   TableName       2019-10-01 Evening   2019-10-01 Morning   2019-10-02 Evening   2019-10-02 Morning
-------------- --------------- -------------------- -------------------- -------------------- --------------------
DatabaseA      TableA          15                   10                   25                   20
DatabaseA      TableB          30                   10                   25                   20
DatabaseA      TableC          NULL                 10                   25                   20

Hogan 5 年前

可以用一个values语句执行多行操作

INSERT [dbo].[DataCount] ([DatabaseName], [TableName], [RowCount], [Date], [DateForAnalysis], [Runtime]) 
   VALUES (N'DatabaseA', N'TableA', 10, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 09:00:00.000' AS DateTime), N'Morning'),
          (N'DatabaseA', N'TableB', 10, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 09:00:00.000' AS DateTime), N'Morning'),
          (N'DatabaseA', N'TableC', 10, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 09:00:00.000' AS DateTime), N'Morning'),
          (N'DatabaseA', N'TableA', 15, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 18:00:00.000' AS DateTime), N'Evening'),
          (N'DatabaseA', N'TableB', 15, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 18:00:00.000' AS DateTime), N'Evening'),
          (N'DatabaseA', N'TableB', 15, CAST(N'2019-10-01' AS Date), CAST(N'2019-10-01 18:00:00.000' AS DateTime), N'Evening'),
          (N'DatabaseA', N'TableA', 20, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 09:00:00.000' AS DateTime), N'Morning'),
          (N'DatabaseA', N'TableB', 20, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 09:00:00.000' AS DateTime), N'Morning'),
          (N'DatabaseA', N'TableC', 20, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 09:00:00.000' AS DateTime), N'Morning'),
          (N'DatabaseA', N'TableA', 25, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 18:00:00.000' AS DateTime), N'Evening'),
          (N'DatabaseA', N'TableB', 25, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 18:00:00.000' AS DateTime), N'Evening'),
          (N'DatabaseA', N'TableC', 25, CAST(N'2019-10-02' AS Date), CAST(N'2019-10-02 18:00:00.000' AS DateTime), N'Evening')
GO