Help required with reverse crosstab query

Hi, I have an Access 2010 database and Im trying to display some data vertically rather than horizontally, Ive googled around and it seems some variation of a reverse crosstab query may work, but I cannot figure it out.

So I have 2 tables called OrderItem and MaterialRequirement that are joined by OrderId.  In the OrderItem table I need to display the [OrderId], [DateRequired] fields and from the MaterialRequirement table I need to display [MaterialType], [Depth], [Width], [LengthA], [LengthB], [LengthC], [LengthD], [LengthE] and [LengthF].  But I need it to display like this:

OrderID | DateRequired | MaterialType | Depth | Width | LengthType | Length

1 | 01/03/2018 | 1 | 100mm | 300mm | A | 6/824
1 | 01/03/2018 | 2 | 100mm | 200mm | B | 9/820
1 | 01/03/2018 | 3 | 100mm | 300mm | C | ANC
1 | 01/03/2018 | 4 | 100mm | 300mm | D | 4/1200
1 | 01/03/2018 | 5 | 100mm | 300mm | E | 6/824
1 | 01/03/2018 | 6 | 100mm | 300mm | F | CTRN
2 | 03/03/2018 | 1 | 500mm | 200mm | A | SWN
2 | 03/03/2018 | 4 | 450mm | 200mm | B | 812
2 | 03/03/2018 | 7 | 600mm | 300mm | C | 41
2 | 03/03/2018 | 8 | 200mm | 300mm | D | 211
2 | 03/03/2018 | 9 | 200mm | 300mm | E | 1334
2 | 03/03/2018 | 1 | 200mm | 300mm | F | 1300

Needs to be grouped on OrderID and sorted by LengthType

Hope you can help.

Thanks.
palsopAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You need a UNION as core query. E.g.

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType FROM yourTable
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType FROM yourTable
) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window

0
palsopAuthor Commented:
Hi,

Thanks for getting back to me.  So should this work?

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType FROM OrderItem INNER JOIN MaterialRequirement ON OrderItem.OrderID = MaterialRequirement.ParentID;

    UNION ALL
    
SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType FROM OrderItem INNER JOIN MaterialRequirement ON OrderItem.OrderID = MaterialRequirement.ParentID;

) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window

0
ste5anSenior DeveloperCommented:
No. There is a severe syntax error: You terminate a SQL statement by using the semi-colon. But as we want a sub-query you need to remove them. E.g.

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window


Please us always table alias names and specify them for all columns also. Can you explain your data model? Cause it looks like that the JOIN could be done outside the sub-query.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

palsopAuthor Commented:
Thanks, how would I add the other Lengths?  LengthC, LengthD, LengthE and LengthF?

By now you have realised that Im not very good at access.
0
ste5anSenior DeveloperCommented:
By repetition...

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthC], "C" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window

0
palsopAuthor Commented:
Thanks.  Almost there, last thing is how do I get it to display it like the attached screenshot
0
palsopAuthor Commented:
Capture.PNG
0
ste5anSenior DeveloperCommented:
By adding the columns to each sub query in the union block.
0
PortletPaulEE Topic AdvisorCommented:
For each length_type seen in that image, you need a subquery within the union query (A to J inclusive):
SELECT
      oi.*, Q.*
FROM (
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthA] AS Length, "A" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthB], "B" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthC], "C" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthD], "D" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthE], "E" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthF], "F" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthG], "G" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthH], "H" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthI], "I" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthJ], "J" AS LengthType FROM MaterialRequirement MR
     ) Q
INNER JOIN OrderItem OI ON Q.OrderID = OI.OrderID
ORDER BY
      Q.OrderID
    , Q.LengthType
;

Open in new window

If columns after [LengthF] don't exist, just output NULL instead
SELECT
      oi.*, Q.*
FROM (
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthA] AS Length, "A" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthB], "B" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthC], "C" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthD], "D" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthE], "E" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthF], "F" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "G" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "H" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "I" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "J" AS LengthType FROM MaterialRequirement MR
     ) Q
INNER JOIN OrderItem OI ON Q.OrderID = OI.OrderID
ORDER BY
      Q.OrderID
    , Q.LengthType
;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.