none
Pivot table RRS feed

  • Question

  • Hello,

    I have a seemingly simple scenario in which I need to present the 'many' values in a one to many relationship as columns in a query.  For example, say I have a table indicating which product lines each employee in a company is responsible for.  In this scenario I'd have an Employee table (with PK EmployeeID), a ProductLine table (with PK ProductLineID) and a table relating the two called EmployeeProductLine (containing only the two fields EmployeeID and ProductLineID which also compose its PK).  In practice an Employee would not be responsible for more than a small arbitrary number of ProductLines but in theory a single Employee could be assigned responsibility for all ProductLines in the the ProductLine table.  

    In this scenario is there a way using Access SQL alone to present a single row for each Employee and the ProductLines for which they are responsible as columns?  In other words a query based on EmployeeProductLine which would display a row for each distinct EmployeeID and "ProductLine #1"..."ProductLine #n" columns where n is highest number of ProductLines that a single employee is currently responsible for.  Thus an Employee responsible for n ProductLines would have n+1 columns (an EmployeeID column and 1 to n ProductLine # columns with the ProductLine column values being ProductLineIDs).  If an Employee is responsible for x ProductLines where x<n, he/she would have nulls where ProductLine # > x.

    I know I can code this creating a temporary table using VBA but I was just curious if there was a more elegant solution using SQL alone.  I've been trying to make something similar work with crosstab queries or pivot table views but have not been succcesful.

    -Bruce

    Monday, August 24, 2015 8:28 PM

Answers

  • A crosstab query can have numbered columns with the Product Line as value.

    Employee 1 2 3 4
    John Product D Product X Product Y
    Bill Product A Product B
    Joe Product N Product P

    Product Y

    Try this to feed the crossab and pivot on Product --

    SELECT EmployeeProductLine.EmployeeID, EmployeeProductLine.ProductLineID, Sum(IIf([XX].[ProductLineID]<=[EmployeeProductLine].[ProductLineID],1,0)) AS Product

    FROM EmployeeProductLine, EmployeeProductLine AS XX

    WHERE (((XX.EmployeeID)=[EmployeeProductLine].[EmployeeID]) AND ((XX.ProductLineID)<=[EmployeeProductLine].[ProductLineID]))

    GROUP BY EmployeeProductLine.EmployeeID, EmployeeProductLine.ProductLineID

    ORDER BY EmployeeProductLine.EmployeeID, EmployeeProductLine.ProductLineID;


    Build a little, test a little



    Monday, August 24, 2015 9:23 PM

All replies

  •  In other words a query based on EmployeeProductLine which would display a row for each distinct EmployeeID and "ProductLine #1"..."ProductLine #n" columns where n is highest number of ProductLines that a single employee is currently responsible for.  

    What about using a crosstab query?

    Build a little, test a little


    Monday, August 24, 2015 8:40 PM
  • Hi Karl,

    As stated previously I thought about using a crosstab query but have been unsuccessful doing so.  It seems like there should be a trivial solution given the trivial two field table it would be based on.

    -Bruce

    Monday, August 24, 2015 8:56 PM
  • A crosstab query can have numbered columns with the Product Line as value.

    Employee 1 2 3 4
    John Product D Product X Product Y
    Bill Product A Product B
    Joe Product N Product P

    Product Y

    Try this to feed the crossab and pivot on Product --

    SELECT EmployeeProductLine.EmployeeID, EmployeeProductLine.ProductLineID, Sum(IIf([XX].[ProductLineID]<=[EmployeeProductLine].[ProductLineID],1,0)) AS Product

    FROM EmployeeProductLine, EmployeeProductLine AS XX

    WHERE (((XX.EmployeeID)=[EmployeeProductLine].[EmployeeID]) AND ((XX.ProductLineID)<=[EmployeeProductLine].[ProductLineID]))

    GROUP BY EmployeeProductLine.EmployeeID, EmployeeProductLine.ProductLineID

    ORDER BY EmployeeProductLine.EmployeeID, EmployeeProductLine.ProductLineID;


    Build a little, test a little



    Monday, August 24, 2015 9:23 PM
  • Hi Karl,

    I had tried something similar before which did not work.  I knew I somehow had to get a 'row number' which when crosstabbed would become a column so I came up with the following:

    SELECT EmployeeProductLine.EmployeeID,
        EmployeeProductLine.ProductLineID, (select count(*)
        from EmployeeProductLine as E
        where E.EmployeeID = EmployeeProductLine.EmployeeID and
            E.ProductLineID <= EmployeeProductLine.ProductLineID)
        AS Row
    FROM EmployeeProductLine
    GROUP BY EmployeeProductLine.EmployeeID,
        EmployeeProductLine.ProductLineID;

    This returned the same rows as the SELECT you posted but unfortunately it did not work as a basis for the needed crosstab.  Your SQL however does work as the basis for a crosstab and gives me the results I need.  Thanks!

    -Bruce

    Tuesday, August 25, 2015 3:35 PM