时间戳

首页 > 解决方案 > SQL Server：如何将行中的不同项目转换为列值

sql - SQL Server：如何将行中的不同项目转换为列值

问题描述

我正在设置一个 T-SQL 查询，它将行转换为列，但是在创建它时卡住了。已在 T-SQL 语句下创建，并使用它，我需要将行转换为列

SELECT 
    OBJECT_NAME,
    CAST(ROUND(AVG(duration) / 1000000.0, 2) AS NUMERIC(36, 2)) AS AVGDuration,
    CONVERT(VARCHAR(11), timestamp, 105), 
    database_name
FROM 
    [DBA].[dbo].[longrun]
GROUP BY 
    OBJECT_NAME, CONVERT(VARCHAR(11), timestamp, 105), database_name
ORDER BY 
    CONVERT(VARCHAR(11), timestamp, 105)

电流输出：

OBJECT_NAME         AVGDuration     Date          database
------------------------------------------------------------
XYZ                 112.35          May 14 2019   X
sp_executesql       60.01           May 14 2019   Y
XYZ                 132.35          May 15 2019   X
sp_executesql       80.01           May 15 2019   Y

日期应转换为列，日期时间是动态值，不应硬编码

预期输出：

Object Name     Database  May 14 2019   May 15 2019  
------------------------------------------------------------
XYZ             X         112.35        132.35
sp_executesql   Y          60.01         80.01

标签： sqlsql-servertsql

解决方案

您可以使用 PIVOT 获得所需的输出 -

WITH CTE(OBJECT_NAME,AVGDuration,Date,[database])
AS
(
    SELECT OBJECT_NAME,
    CAST(round(avg(duration)/1000000.0,2) as numeric(36,2)) as AVGDuration,
    CONVERT(VARCHAR(11), timestamp, 105) Date,
    database_name
    FROM [DBA].[dbo].[longrun]
    GROUP BY OBJECT_NAME,CONVERT(VARCHAR(11), timestamp, 105),database_name
    ORDER BY CONVERT(VARCHAR(11), timestamp, 105)
)

SELECT OBJECT_NAME,[database],[May 14 2019],[May 15 2019]
FROM
(
    SELECT * FROM CTE
)A
PIVOT(
    MAX(AVGDuration)
    FOR Date IN([May 14 2019],[May 15 2019])
)PVT

如果是动态日期列表，您可以试试这个 -

DECLARE @ColumnList VARCHAR(MAX)
DECLARE @sqlCommand VARCHAR(MAX)


;WITH CTE(Date)
AS
(
    SELECT DISTINCT CONVERT(VARCHAR(11), timestamp, 105) Date
    FROM [DBA].[dbo].[longrun]
)

SELECT  @ColumnList = STUFF((SELECT  ('],[' +  A.Date)
        FROM CTE A
        ORDER BY CAST(A.Date AS DATE) 
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')+']'
FROM CTE


SET @sqlCommand= 
N'SELECT OBJECT_NAME,[database],'+SUBSTRING(@ColumnList,2,LEN(@ColumnList))+'
FROM
(
    SELECT * FROM 
    (
        SELECT OBJECT_NAME,
        CAST(round(avg(duration)/1000000.0,2) as numeric(36,2)) as AVGDuration,
        CONVERT(VARCHAR(11), timestamp, 105) Date,
        database_name
        FROM [DBA].[dbo].[longrun]
        GROUP BY OBJECT_NAME,CONVERT(VARCHAR(11), timestamp, 105),database_name
        ORDER BY CONVERT(VARCHAR(11), timestamp, 105)
    )
    CTE
)A
PIVOT(
    MAX(AVGDuration)
    FOR Date IN('+SUBSTRING(@ColumnList,2,LEN(@ColumnList))+')
)PVT'


--PRINT @sqlCommand
EXEC (@sqlCommand)

推荐阅读