Use of PIVOT without temporary table

Question

Problem summary: I'm unfamiliar with PIVOT. I want the same outcome of the code below:

DECLARE @fillerUnits TABLE (
    ReportHour datetime,
    FillerName varchar(20),
    FillerUnits int
)

INSERT INTO @fillerUnits
    SELECT
        FillerCounts2.ReportHour,
        Fillers.Name,
        FillerCounts2.Units
    FROM
    [Production].[dbo].[FillerCounts2]
    INNER JOIN Fillers ON FillerID = Fillers.ID

SELECT *
FROM @fillerUnits
PIVOT (
    SUM(FillerUnits)
    FOR FillerName IN (
      [Fed 1]
      ,[Fed 2]
      ,[Fed 3]
      ))
    AS q

except I want to use it in a VIEW, so I can't use the temporary table, I have to select and pivot directly from Production.dbo.FillerCounts2, like so:

SELECT
    FillerCounts2.ReportHour,
    Fillers.Name,
    FillerCounts2.Units
FROM
    [Production].[dbo].[FillerCounts2]
    INNER JOIN Fillers ON FillerID = Fillers.ID
PIVOT (
    SUM(Units)
    FOR Fillers.Name IN (
      [Fed 1]
      ,[Fed 2]
      ,[Fed 3]
      ))
    AS p

However, when I do that it gives me errors on my column names,

"the multipart identifier (column name) could not be bound"

and on my INNER JOIN criterion,

"Ambiguous column name FillerID"

I would appreciate if someone could effect what I'm trying to do, and maybe explain how to properly select columns from a pivot.

Answer 1

My own answer:

I consulted the documentation at https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16 and came up with the solution

SELECT *
FROM (
    SELECT
        FillerCounts2.ReportHour,
        Fillers.Name as 'FillerName',
        FillerCounts2.Units
    FROM
        [Production].[dbo].[FillerCounts2]
        INNER JOIN Fillers ON FillerID = Fillers.ID
    ) as t
PIVOT (
    SUM(Units)
    FOR t.FillerName IN (
      [Fed 1]
      ,[Fed 2]
      ,[Fed 3]
      ))
    AS p