Column Pivot

Question

Post reply

Column Pivot

gjoelson 29755

SSC Eights!

Points: 848

July 2, 2023 at 9:43 pm

#4225271

Hi ,

I need to do one of these ghastly pivots again... and looking for some help.

Here is my below query that show the column called "Oven", in one column, but I need to Pivot this to show across the top as Oven1, Oven2, Oven3 etc. with the value COUNT (*) as Runs

SELECT 
    oven,
    DATEPART(year, [Run end Time]) AS Year,
    DATEPART(month, [Run end Time]) AS Month,
    COUNT(*) as Runs
FROM 
   [dbo].[Prod_ShiftSummary] 
   where office = 'Baytown'  
GROUP BY 
    oven, 
    DATEPART(year, [Run end Time]),
    DATEPART(month, [Run end Time])
ORDER BY 
    Year, 
    Month, 
    Runs DESC;

Example:

oven

Here below is a temp table and some sample data to test - thank you.

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

/****** Object:  Table [dbo].[Prod_ShiftSummary]    Script Date: 6/5/2023 5:01:37 PM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE #mytable(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EntryDate] [datetime] NULL,
[Office] [nvarchar](max) NULL,
[Oven] [nvarchar](max) NULL,
[Run] [nvarchar](max) NULL,
[Run Start Time] [datetime] NULL,
[Run end Time] [datetime] NULL,
[shift] [nvarchar](max) NULL,
[Username] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Sample data

insert into #mytable
(Entrydate,Office,Oven,[Run Start Time],[Run end Time])
SELECT '2023-05-25 10:37:00.000','Baytown','1','2023-05-25 10:37:00.000','2023-05-25 11:37:00.000' UNION ALL
SELECT '2023-05-26 10:37:00.000','Baytown','3','2023-05-26 12:21:00.000','2023-05-26 13:12:00.000' UNION ALL
SELECT '2023-05-27 10:37:00.000','Baytown','4','2023-05-26 14:11:00.000','2023-05-26 15:37:00.000' UNION ALL
SELECT '2023-05-28 10:37:00.000','Baytown','6','2023-05-26 15:11:00.000','2023-05-26 15:22:00.000' UNION ALL
SELECT '2023-05-29 10:37:00.000','Baytown','6','2023-05-27 10:33:00.000','2023-05-27 13:44:00.000'

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

thanks!

This topic was modified 1 year, 4 months ago by gjoelson 29755.
This topic was modified 1 year, 4 months ago by gjoelson 29755.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

What's the max oven number and what do you want to count if a "run" straddles the last day and the first day of the month? For example, do you want to count only the run start times?

And let me know if you want to take it up a notch. To do that, I'll need the names of 3 more offices (or, as many as you have if less).

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

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 2

Jeff, there are 7 ovens only. I only want to count the [Run end Time], so if that falls on the beginning of next month then exclude it.

i.e the [Run end Time] is what is counted.

Thank you.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Ok... the following provides what you asked for. Give me 3 more Office names and I'll give you what I think they'll ask you for in the future and it'll make you look like a hero.

DECLARE  @Office NVARCHAR(100) = N'BAYTOWN'
        ,@Year   SMALLINT      = 2023
;
   WITH
ctePreAgg AS
(--===== This preaggregation makes things run almost twice as fast.
 SELECT  Office
        ,Oven
        ,Month    = DATEDIFF(mm,0,[Run end Time]) --Trust me ;>)
        ,RunCount = COUNT(*)
   FROM #mytable
  WHERE Office = @Office
    AND [Run end Time] >= DATEFROMPARTS(@Year  ,1,1) --This is the right way to isolate date ranges.
    AND [Run end Time] <  DATEFROMPARTS(@Year+1,1,1)
  GROUP BY Office,Oven,DATEDIFF(mm,0,[Run end Time])
)
 SELECT  Month = CONVERT(CHAR(3),DATEADD(mm,pa.Month,0),107)
        ,Oven1 = SUM(IIF(Oven = 1, pa.RunCount, 0)) --These make the "CROSSTAB",
        ,Oven2 = SUM(IIF(Oven = 2, pa.RunCount, 0)) --which as some serious advantages over PIVOT
        ,Oven3 = SUM(IIF(Oven = 3, pa.RunCount, 0)) --It's all "CPR" (Copy, Paste, Replace)
        ,Oven4 = SUM(IIF(Oven = 4, pa.RunCount, 0))
        ,Oven5 = SUM(IIF(Oven = 5, pa.RunCount, 0))
        ,Oven6 = SUM(IIF(Oven = 6, pa.RunCount, 0))
        ,Oven7 = SUM(IIF(Oven = 7, pa.RunCount, 0))
   FROM ctePreAgg pa
  GROUP BY pa.Office,pa.Month
  ORDER BY pa.Office,pa.Month
;

Results using only the data provided in the original post.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

p.s. I fogot to do the exclusion if the start of the run is in the month before the end of the run. That's easy to do but let's first see if the format and the rest of it is what you want.

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

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 5

Jeff, Perfect ! thanks so much for the help, I will run with this and see how it goes when moving to the next month.

but this looks great.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

gjoelson 29755 wrote:

Jeff, Perfect ! thanks so much for the help, I will run with this and see how it goes when moving to the next month.
but this looks great.

Thanks for the feedback. The real key now is, given a similar request on a different table, do you understand the code well enough to do such a thing on your own?

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

kaj SSCommitted Points: 1605 More actions · Answer 7

One comment: I hope the example table isn't an exact replica of your production table, because to use that many columns defined as nvarchar(max) just to be on the safe side is a very bad habit. Using that kind of column type comes with a whole slew of caveats.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

kaj wrote:

One comment: I hope the example table isn't an exact replica of your production table, because to use that many columns defined as nvarchar(max) just to be on the safe side is a very bad habit. Using that kind of column type comes with a whole slew of caveats.

Heh... Ditto that. That's where I was going next (along with a couple of really cool/ simple reporting hacks) but I couldn't get the OP to give me a couple of other office names. There has to be some interest in a offer to help. 🙂

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