Dynamic Pivot in SQL for multiple column

Question

My query for job running in SSIS

SELECT
    CONVERT(varchar(12), GETDATE(), 110) AS Date
,   j.name AS job_name
,   CONVERT(varchar(10), ja.run_requested_date, 108) AS Start_Time
,   CONVERT(varchar(10), CONVERT(datetime, RTRIM(19000101)) 
        + (jh.run_duration * 9 + jh.run_duration % 10000 * 6
        + jh.run_duration % 100 * 10) / 216e4, 108) AS run_duration
,   (ja.run_requested_date + (CONVERT(varchar(10), CONVERT(datetime, RTRIM(19000101)) 
    + (jh.run_duration * 9
    + jh.run_duration
    % 10000 * 6
    + jh.run_duration % 100
    * 10) / 216e4, 108))) AS Completion_Time
FROM
    (msdb.dbo.sysjobactivity ja
        LEFT JOIN msdb.dbo.sysjobhistory jh
        ON ja.job_history_id = jh.instance_id
    )
    JOIN msdb.dbo.sysjobs_view j
    ON ja.job_id = j.job_id
WHERE
    ja.session_id = (
                        SELECT
                            MAX(session_id)
                        FROM
                            msdb.dbo.sysjobactivity
                    )
    AND j.name IN ('a', 'b', 'c', 'd', 'e');

I want this query to get pivoted as attached image. Also the date column needs to be from start of month to till date.

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Matt, Aug 20 '16 at 19:32
there are a lot of examples on dynamic pivot on this website I just tagged one of them for you. But to do this in SSIS you have to ask what do you want to do with the table? if you use this technique the dataflow is really out of the question because SSIS doesn't handle dynamic columns very well. But you could still use a script task, or a script component.... — Matt, Aug 20 '16 at 19:34

score 0 · Answer 1 · answered Aug 20 '16 at 20:04

This may help

CREATE VIEW vwJobs
AS
    SELECT  CONVERT(VARCHAR(12), GETDATE(), 110) AS Date ,
            j.name AS job_name ,
            CONVERT(VARCHAR(10), ja.run_requested_date, 108) AS Start_Time ,
            CONVERT(VARCHAR(10), CONVERT(DATETIME, RTRIM(19000101))
            + ( jh.run_duration * 9 + jh.run_duration % 10000 * 6
                + jh.run_duration % 100 * 10 ) / 216e4, 108) AS run_duration ,
            ( ja.run_requested_date
              + ( CONVERT(VARCHAR(10), CONVERT(DATETIME, RTRIM(19000101))
                  + ( jh.run_duration * 9 + jh.run_duration % 10000 * 6
                      + jh.run_duration % 100 * 10 ) / 216e4, 108) ) ) AS Completion_Time
    FROM    ( msdb.dbo.sysjobactivity ja
              LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
            )
            JOIN msdb.dbo.sysjobs_view j ON ja.job_id = j.job_id
    WHERE   ja.session_id = ( SELECT    MAX(session_id)
                              FROM      msdb.dbo.sysjobactivity
                            )
            AND j.name IN ( 'a', 'b', 'c', 'd', 'e' );

GO

--QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnNamesInPivot AS NVARCHAR(MAX);

--Get distinct values of PIVOT Column 
SELECT TOP 100 PERCENT
        @ColumnNamesInPivot = ISNULL(@ColumnNamesInPivot + ',', '')
        + QUOTENAME([Date])
FROM    ( SELECT    DISTINCT
                    [Date]
          FROM      vwJobs
        ) AS P
ORDER BY [Date];

--Prepare the PIVOT query using the dynamic query
SELECT  @DynamicPivotQuery = N'Select job_name, ''Start_Time'' Time,'
        + @ColumnNamesInPivot + ' 
            FROM    ( SELECT * 
          FROM      vwJobs
        ) AS SourceTable PIVOT( MAX(Start_Time) FOR [Date] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable
    UNION ALL
    Select job_name, ''Duration'' Time,' + @ColumnNamesInPivot + ' 
            FROM    ( SELECT * 
          FROM      vwJobs
        ) AS SourceTable PIVOT( MAX(run_duration) FOR [Date] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable
    UNION ALL
    Select job_name, ''End_Time'' Time,' + @ColumnNamesInPivot + ' 
            FROM    ( SELECT * 
          FROM      vwJobs
        ) AS SourceTable PIVOT( MAX(Completion_Time) FOR [Date] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable    
        Order by job_name
        ';

--SELECT  @DynamicPivotQuery;
EXEC sp_executesql @DynamicPivotQuery;

jarred-Not able to see the result when i print @DynamicPivotQuery. — rahul, Aug 21 '16 at 05:30
@jarred-I need to store the pivoted result into a object ,which i will be displaying in Email body using script task.please guide for the same. — rahul, Aug 21 '16 at 05:32
@rahul, Print will be empty if DynamicPivotQuery is NULL. DynamicPivotQuery will be NULL if vwJobs returns rows.So, make sure that view vwJobs has records. — Yared, Aug 21 '16 at 08:26

score 0 · Answer 2 · answered Aug 20 '16 at 20:14

0

It's probably going to be lot more simple if you have one column for month (including year) and then 31 columns for the days. You don't need anything dynamic. If you would have a dynamic column names, what would you even do with those in SSIS?

answered Aug 20 '16 at 20:14

James Z

12,209
10
24
44

Yared · Answer 3 · 2016-08-21T17:27:27.330

UPDATE: - GetDate() is selected as date column in the view. - Updated PIVOT query so that multiple rows are not returned.

So, here is the update query:

CREATE VIEW vwJobs
AS
    SELECT  CONVERT(VARCHAR(12), run_requested_date, 110) AS Date ,
            j.name AS job_name ,
            CONVERT(VARCHAR(10), ja.run_requested_date, 108) AS Start_Time ,

            CONVERT(VARCHAR(10), CONVERT(DATETIME, RTRIM(19000101))
            + ( jh.run_duration * 9 + jh.run_duration % 10000 * 6
                + jh.run_duration % 100 * 10 ) / 216e4, 108) AS run_duration ,

            ( ja.run_requested_date
              + ( CONVERT(VARCHAR(10), CONVERT(DATETIME, RTRIM(19000101))
                  + ( jh.run_duration * 9 + jh.run_duration % 10000 * 6
                      + jh.run_duration % 100 * 10 ) / 216e4, 108) ) ) AS Completion_Time
    FROM    ( msdb.dbo.sysjobactivity ja
              LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
            )
            JOIN msdb.dbo.sysjobs_view j ON ja.job_id = j.job_id
            JOIN ( SELECT   CONVERT(DATE, run_requested_date) date ,
                        MAX(session_id) session_id
               FROM     msdb.dbo.sysjobactivity
               WHERE    run_requested_date IS NOT NULL
               GROUP BY CONVERT(DATE, run_requested_date)
             ) ss ON ja.session_id = ss.session_id

            AND j.name IN ( 'a', 'b', 'c', 'd', 'e' );

GO


--QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnNamesInPivot AS NVARCHAR(MAX);

--Get distinct values of PIVOT Column 
SELECT TOP 100 PERCENT
        @ColumnNamesInPivot = ISNULL(@ColumnNamesInPivot + ',', '')
        + QUOTENAME([Date])
FROM    ( SELECT    DISTINCT
                    [Date]
          FROM      vwJobs
        ) AS P
ORDER BY [Date];

--Prepare the PIVOT query using the dynamic query
SELECT  @DynamicPivotQuery = N'Select job_name, ''Start_Time'' Time,'
        + @ColumnNamesInPivot + ' 
            FROM    ( SELECT Date, job_name, Start_Time
          FROM      vwJobs
        ) AS SourceTable PIVOT( MAX(Start_Time) FOR [Date] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable
    UNION ALL
    Select job_name, ''Duration'' Time,' + @ColumnNamesInPivot + ' 
            FROM    ( SELECT Date, job_name, run_duration 
          FROM      vwJobs
        ) AS SourceTable PIVOT( MAX(run_duration) FOR [Date] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable
    UNION ALL
    Select job_name, ''End_Time'' Time,' + @ColumnNamesInPivot + ' 
            FROM    ( SELECT Date, job_name, Completion_Time  
          FROM      vwJobs
        ) AS SourceTable PIVOT( MAX(Completion_Time) FOR [Date] IN ('
        + @ColumnNamesInPivot + ') ) AS PVTTable    
        Order by job_name
        ';

--PRINT  @DynamicPivotQuery;
EXEC sp_executesql @DynamicPivotQuery;

Result in my local:

Dynamic Pivot in SQL for multiple column

3 Answers3