Pivot Table round to 1 deciaml.

• ipawelkon

  SSC Veteran

  Points: 263

  More actions

  January 17, 2019 at 8:54 am

  #370248

  Hi All,
  I am new to forum, so forgive  me if I post something incorrectly.

  I have the following qry. However I would like to be able to round output to 1 decimal place only.

  Qry:
  
SELECT *
FROM
(
  SELECT

      datepart(week,t_stamp)[week],
    [Line],
    [TaktTime]

    FROM [dbo].[TaktTime]
    ) AS SourceTable PIVOT(AVG([TaktTime]) FOR [Line] IN([Truck (Galway)],
                   [Trailer (Galway)],
                   [E-Evap (Galway)])) AS PivotTable

  WHERE [Trailer (Galway)] IS NOT NULL
AND [Truck (Galway)] IS NOT NULL
AND [E-Evap (Galway)] IS NOT NULL
ORDER BY [week]


  Output:
  

  So I would like to have for example 93.3 only and not 93.33333333.
  So any help will be much appreciate. 
  Thanks in advance
  Pawel

• Mike01

  SSChampion

  Points: 11395

  More actions

  January 17, 2019 at 9:01 am

  #2019223

  There is no sample data, so I'm not going to generate any, but have you tried Round(AVG([TaktTime],1) instead of AVG([TaktTime]?

  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/

• ipawelkon

  SSC Veteran

  Points: 263

  More actions

  January 17, 2019 at 1:12 pm

  #2019256

  Hi MIke01

  Thanks for your replay and link how I should place questions with data attached.
  I am new to forum and SQL world 🙂

  I tried Round before AVG but I am getting the following error.

  Msg 195, Level 15, State 1, Line 11
  'Round' is not a recognized aggregate function.

  I will populate all information later, including data based on your link.
  However I fixed this issue by changing Takt "Date Type" from float to int and it working fine now.
  However I still would like to understand, if I will have float Data Type how to make it work.

  Regards
  Pawel

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  January 17, 2019 at 1:22 pm

  #2019260

  I think you can cast the AVG([TaktTime]) to decimal(9,1):
  SELECT *
    FROM
  (
  SELECT

    datepart(week,t_stamp)[week],
    [Line],
    [TaktTime]

  FROM [dbo].[TaktTime]
  ) AS SourceTable PIVOT(CONVERT(decimal(9,1),AVG([TaktTime])) FOR [Line] IN([Truck (Galway)],
       [Trailer (Galway)],
       [E-Evap (Galway)])) AS PivotTable
WHERE [Trailer (Galway)] IS NOT NULL
AND [Truck (Galway)] IS NOT NULL
AND [E-Evap (Galway)] IS NOT NULL
ORDER BY [week]


• ipawelkon

  SSC Veteran

  Points: 263

  More actions

  January 18, 2019 at 1:32 am

  #2019337

  Thanks Jonathan for you replay.
  Unfortunately this solution is not working too.
  "Msg 156, Level 15, State 1, Line 11
  Incorrect syntax near the keyword 'CONVERT'."

  I will populate dataset from SQL as per link provided by Mike and then maybe it will be easier to define this.

  Regards
  Pawel

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  January 18, 2019 at 4:28 am

  #2019342

  ipawelkon - Friday, January 18, 2019 1:32 AM

  Thanks Jonathan for you replay.
  Unfortunately this solution is not working too.
  "Msg 156, Level 15, State 1, Line 11
  Incorrect syntax near the keyword 'CONVERT'."

  I will populate dataset from SQL as per link provided by Mike and then maybe it will be easier to define this.

  Regards
  Pawel

  You need to replace your SELECT * with the column names and convert them to decimal:
  SELECT [week], [Line], [TaktTime],
       CONVERT(decimal(9,1), [Truck (Galway)])   AS [Truck (Galway)],
       CONVERT(decimal(9,1), [Trailer (Galway)]) AS [Trailer (Galway)],
       CONVERT(decimal(9,1), [E-Evap (Galway)])  AS [E-Evap (Galway)]
FROM ...

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  January 18, 2019 at 4:30 am

  #2019343

  ipawelkon - Friday, January 18, 2019 1:32 AM

  Thanks Jonathan for you replay.
  Unfortunately this solution is not working too.
  "Msg 156, Level 15, State 1, Line 11
  Incorrect syntax near the keyword 'CONVERT'."

  I will populate dataset from SQL as per link provided by Mike and then maybe it will be easier to define this.

  Regards
  Pawel

  Kindly check the below query and confirm whether it is working or not?

  SELECT *
FROM
(
SELECT

    datepart(week,t_stamp)[week],
  ROUND([Line],1), /* Modified this columns */
  ROUND([TaktTime],1)  /* Modified this columns */

  FROM [dbo].[TaktTime]
  ) AS SourceTable PIVOT(AVG([TaktTime]) FOR [Line] IN([Truck (Galway)],
       [Trailer (Galway)],
       [E-Evap (Galway)])) AS PivotTable

  WHERE [Trailer (Galway)] IS NOT NULL
AND [Truck (Galway)] IS NOT NULL
AND [E-Evap (Galway)] IS NOT NULL
ORDER BY [week]

  Saravanan

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  January 18, 2019 at 4:44 am

  #2019344

  saravanatn - Friday, January 18, 2019 4:30 AM

  ipawelkon - Friday, January 18, 2019 1:32 AM

  Thanks Jonathan for you replay.
  Unfortunately this solution is not working too.
  "Msg 156, Level 15, State 1, Line 11
  Incorrect syntax near the keyword 'CONVERT'."

  I will populate dataset from SQL as per link provided by Mike and then maybe it will be easier to define this.

  Regards
  Pawel

  Kindly check the below query and confirm whether it is working or not?

  SELECT *
FROM
(
SELECT

    datepart(week,t_stamp)[week],
  ROUND([Line],1), /* Modified this columns */
  ROUND([TaktTime],1)  /* Modified this columns */

  FROM [dbo].[TaktTime]
  ) AS SourceTable PIVOT(AVG([TaktTime]) FOR [Line] IN([Truck (Galway)],
       [Trailer (Galway)],
       [E-Evap (Galway)])) AS PivotTable

  WHERE [Trailer (Galway)] IS NOT NULL
AND [Truck (Galway)] IS NOT NULL
AND [E-Evap (Galway)] IS NOT NULL
ORDER BY [week]

  I don't have any way to check it's working as you didn't supply any DDL or data.
  Did you try to do the convert/rounding in the top most select?

• ipawelkon

  SSC Veteran

  Points: 263

  More actions

  January 18, 2019 at 6:36 am

  #2019353

  HI Guys,

  Thanks for your replay, but I think I need to start from the beginning and populate all information that are need it.

  So based on Mike's link I tried to create file to help you understand my data. But even when I followed guideline in from the link I can't make it work.
  Can you have a look on what I am doing wrong please.

  
--===== 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,
        TaktTime    int
        )

  --===== 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


• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  January 18, 2019 at 6:53 am

  #2019357

  This works with your data:
  SELECT [week],
   convert(decimal(9,1),[Truck (Galway)]) [Truck (Galway)],
   convert(decimal(9,1),[Trailer (Galway)]) [Trailer (Galway)],
   convert(decimal(9,1),[E-Evap (Galway)]) [E-Evap (Galway)]
FROM
(
SELECT
  datepart(week,t_stamp)[week],
  [Line],
  [TaktTime]
FROM #mytable t
  ) AS SourceTable PIVOT(AVG([TaktTime]) FOR [Line] IN([Truck (Galway)],
       [Trailer (Galway)],
       [E-Evap (Galway)])) AS PivotTable
 WHERE [Trailer (Galway)] IS NOT NULL
   AND [Truck (Galway)] IS NOT NULL
   AND [E-Evap (Galway)] IS NOT NULL

• ipawelkon

  SSC Veteran

  Points: 263

  More actions

  January 18, 2019 at 7:11 am

  #2019360

  Great you are an legend 🙂
  Thanks a lot.

  I tried to run this qry from link from Mike: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  See below.

  But it give me this error: 
  

  
  Msg 8152, Level 16, State 4, Line 21
  String or binary data would be truncated.
  The statement has been terminated.
  

  I just like to ensure I can do basic qry when ask for help.

  
--===== 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,
   TaktTime  int
   )

  --===== 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

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  January 18, 2019 at 7:18 am

  #2019363

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

  Great you are an legend 🙂
  Thanks a lot.

  I tried to run this qry from link from Mike: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  See below.

  But it give me this error: 
  

  
  Msg 8152, Level 16, State 4, Line 21
  String or binary data would be truncated.
  The statement has been terminated.
  

  I just like to ensure I can do basic qry when ask for help.

  
--===== 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,
   TaktTime  int
   )

  --===== 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

  That's a problem with the table definition, For column Line   nvarchar, that would make it a 1 character column.
  Replace it with this:
  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
   )
  I also changed the int to float so the old query has a lot of decimal places.