CODEWITHSUNDEEP
sqlsql-servert-sql
Sanky
Sanky

Reputation: 41

SQL Server : how to convert distinct item in row to column values

I am setting up a T-SQL query which will convert rows to column however gets stuck in creating this. Have created below T-SQL statements, and using this and i need to convert row to column

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)

Current output:

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

Dates should be converted to column and Datetime is dynamic value and it should not be hard coded

Expected output:

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

Upvotes: 0

Views: 1211

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

You can use PIVOT to get your desired output-

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

In case of Dynamic Date list, you can try this-

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)

Upvotes: 1

Related Questions