Post reply

Dynamic pivot tables

  • January 18, 2019 at 8:05 am

    #409442

    Hello,

    I would like to setup dynamic pivot table and I am getting errors, I need to average and group table by week to eliminate all the nulls . So when I add "Group by week" then error occurs. Without Group by line data is not useful for bar charts.

    See data below:


    --===== If the test table already exists, drop it
     IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
      DROP TABLE #mytable

    --===== Create the test table with

    CREATE TABLE #mytable
      (
      id  INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
      t_stamp datetime,
      Line  nvarchar(100),
      TaktTime float
      )

    --===== All Inserts into the IDENTITY column
    SET IDENTITY_INSERT #mytable ON

    --===== Insert the test data into the test table
    INSERT INTO #mytable
     (id, t_stamp, Line, TaktTime)
    SELECT '573195','Jan 16 2019 11:45AM','Trailer (Arecibo)','100' UNION ALL
    SELECT '573181','Jan 16 2019 10:12AM','Truck (Galway)','95' UNION ALL
    SELECT '573179','Jan 16 2019 6:52AM','Trailer (Galway)','93' UNION ALL
    SELECT '573194','Jan 15 2019 11:45AM','Trailer (Arecibo)','100' UNION ALL
    SELECT '573189','Jan 15 2019 11:45AM','Truck (Hastings)','94' UNION ALL
    SELECT '573180','Jan 15 2019 10:12AM','Truck (Galway)','89' UNION ALL
    SELECT '573177','Jan 15 2019 6:47AM','Trailer (Galway)','92' UNION ALL
    SELECT '573174','Jan 14 2019 12:48PM','Truck (Galway)','75' UNION ALL
    SELECT '573188','Jan 14 2019 11:45AM','Truck (Hastings)','94' UNION ALL
    SELECT '573193','Jan 14 2019 11:45AM','Trailer (Arecibo)','100' UNION ALL
    SELECT '573172','Jan 14 2019 7:10AM','E-Evap (Galway)','96' UNION ALL
    SELECT '573176','Jan 14 2019 6:47AM','Trailer (Galway)','98' UNION ALL
    SELECT '573187','Jan 12 2019 11:45AM','Truck (Hastings)','97' UNION ALL
    SELECT '573171','Jan 11 2019 1:53PM','E-Evap (Galway)','99' UNION ALL
    SELECT '573173','Jan 11 2019 12:48PM','Truck (Galway)','84' UNION ALL
    SELECT '573186','Jan 11 2019 11:45AM','Truck (Hastings)','69' UNION ALL
    SELECT '573192','Jan 11 2019 11:45AM','Trailer (Arecibo)','100' UNION ALL
    SELECT '573175','Jan 11 2019 6:47AM','Trailer (Galway)','96' UNION ALL
    SELECT '573168','Jan 10 2019 4:04PM','Truck (Galway)','83' UNION ALL
    SELECT '573185','Jan 10 2019 11:45AM','Truck (Hastings)','69' UNION ALL
    SELECT '573191','Jan 10 2019 11:45AM','Trailer (Arecibo)','100' UNION ALL
    SELECT '573170','Jan 10 2019 10:03AM','E-Evap (Galway)','98' UNION ALL
    SELECT '573169','Jan 10 2019 6:48AM','Trailer (Galway)','95' UNION ALL
    SELECT '573167','Jan 9 2019 12:17PM','Truck (Galway)','81' UNION ALL
    SELECT '573184','Jan 9 2019 11:45AM','Truck (Hastings)','47' UNION ALL
    SELECT '573190','Jan 9 2019 11:45AM','Trailer (Arecibo)','82' UNION ALL
    SELECT '573166','Jan 9 2019 7:27AM','E-Evap (Galway)','99' UNION ALL
    SELECT '573164','Jan 9 2019 6:47AM','Trailer (Galway)','98' UNION ALL
    SELECT '573163','Jan 8 2019 1:42PM','E-Evap (Galway)','97' UNION ALL
    SELECT '573183','Jan 8 2019 11:45AM','Truck (Hastings)','56'

    --===== Set the identity insert back to normal
    SET IDENTITY_INSERT #mytable OFF

    -----===========QRY for above table.=================================

    --This parameter will hold the dynamically created SQL script
    DECLARE @SQLQuery AS NVARCHAR(MAX)
    --This parameter will hold the Pivoted Column values
    DECLARE @PivotColumns AS NVARCHAR(MAX)
    --Generate the list of Line names that will become headers

    SELECT @PivotColumns= (COALESCE(@PivotColumns + ',','')) + QUOTENAME(Line)
    FROM TempDB..#mytable

    GROUP BY Line

    --LIST ALL FILEDS EXCEPT PIVOT COLUMN

    SET @SQLQuery =
     N'SELECT datepart(week,t_stamp)[week],' + @PivotColumns + ' FROM TempDB..#mytable
    ---GROUP BY [week]                    --========= working without this statement
     PIVOT(AVG([TaktTime])
      FOR [Line] IN (' + @PivotColumns + ')) AS Q'

    /* UNCOMMENT TO SEE THE DYNAMICALLY CREATED SQL STATEMENT */

    EXEC sp_executesql @sqlquery

    Thanks in advance
    Pawel

  • January 18, 2019 at 9:51 am

    #2019386

    What  are the expected results?  Try this, just a wild guess

    SET @sqlquery =
    N'SELECT [week],' + @PivotColumns + '

    FROM (select datepart(week,t_stamp) [week],t_stamp, Line, TaktTime
             from TempDB..#mytable) X
    PIVOT(AVG([TaktTime])
    FOR [Line] IN (' + @PivotColumns + ')) AS Q'

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 18, 2019 at 11:25 am

    #2019402

    Hi,

    I want to eliminate all nulls average by week and display like this. I know how to do this in manual pivot but not as dynamic pivot.

    Regards
    Pawel

  • January 18, 2019 at 3:19 pm

    #2019426

    ipawelkon - Friday, January 18, 2019 11:25 AM

    Hi,

    I want to eliminate all nulls average by week and display like this. I know how to do this in manual pivot but not as dynamic pivot.

    Regards
    Pawel

    See the following article...
    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply