1

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.

1 Answer 1

3

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
1
  • Yes, the PIVOT operator needs to be applied to a single dataset, so in your case you accomplished joining your two datasets within a subquery such that the PIVOT can be applied to the subquery, a single dataset. A CTE would've also worked equally.
    – J.D.
    Jul 12, 2022 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.