How can I get SQL results that resemble a pivot table

Question

I am trying to get back some results from a database and it needs to be returned in a very specific way, similar to a pivot table but from what I've been reading about them I don't know if it applies since I do not need an aggregate. This is to display RFQ information in a report. The database structure is complicated but I will use a simplified example.

RFQ table
----------------
RFQID  |  Requestor
----------------
555    |  789
777    |  789

Item table
--------------------------
RFQID  |  Line  |  ItemID
--------------------------
555    |  1     |  P07
777    |  1     |  P07

ItemQuantity table
------------------------------------
RFQID  |  Line  |  Quantity  |  Unit
------------------------------------
555    |  1     |  5         |  In
555    |  1     |  10        |  In
555    |  1     |  15        |  In
777    |  1     |  30        |  In
777    |  1     |  50        |  In

Vendor table
-----------------------------
RFQID  |  Line  |  VendorID
-----------------------------
555     |  1     |  Speedy
777     |  1     |  SlowPoke

So if I enter a query like this:

SELECT M.RFQID, Requester, I.Line, ItemID, Quantity, Unit, VendorID
    FROM RFQ M
        JOIN Item I ON M.RFQID = I.RFQID 
        JOIN Vendor V ON I.Line = V.Line AND I.RFQID = V.RFQID
        JOIN ItemQuantity IQ ON IQ.Line = V.Line AND IQ.RFQID = V.RFQID
WHERE ItemID IN ('P07', 'P08')
ORDER BY M.RFQID DESC

Which will produce:

555  |  789  |  1  |  P07  |  5  |  In  |  Speedy
555  |  789  |  1  |  P07  |  10 |  In  |  Speedy
555  |  789  |  1  |  P07  |  15 |  In  |  Speedy
777  |  789  |  1  |  P07  |  30 |  In  |  SlowPoke
777  |  789  |  1  |  P07  |  50 |  In  |  SlowPoke

I need to get:

RFQID| Requestor | Line | ItemID | Quantity row1 | Unit row1 | Quantity row2 | Unit row2 | Quantity row3 | Unit row3 | VendorID
555  |  789      |  1   |  P07   |  5            |  In       |  10           |       In  |  15           |  In       |  Speedy
777  |  789      |  1   |  P07   |  30           |  In       |  50           |       In  |  NULL         |  NULL     |  SlowPoke

Is this possible? I know I can write a SP using a cursor but I wish to avoid that route if possible.

EDIT: This is part of a report that: 1) The whole company uses 2) Consists of 14 datasets 3) Is so customized that I doubt there is a report writer that can produce it. 4) It exists in Access 2003 which we wish to get rid of 5) I have almost the whole thing done with the PrintDocument with all the attributes table based so it can be modified without compiling anything.

How did you arrive at that final result? Can you label the columns, so I can get an idea of what it is you're trying to construct? Because it doesn't make sense to me what transformation you're trying to make between the relational format and the final version — Xedni, Aug 31 '17 at 19:49
@John you're PIVOT is the way to go - you can use PIVOT without truly aggregating data, just use MAX() around the quantity fields. If you will not always have the same values for quantity, you'll need to resort to dynamic pivot tables — Eli, Aug 31 '17 at 19:51
@Xedni the 5/10/15 values are seemingly coming from itemquantity table — Eli, Aug 31 '17 at 19:52
Thanks @Eli, that doesn't really elucidate what the end-game is here. We grouping by everything but `quantity` and `unit`? What are those pivoted columns named? — Xedni, Aug 31 '17 at 19:55
@Eli - The quantities may or may not be different. I'll add more data to help clear it up a little. — John, Aug 31 '17 at 20:05
@Eli - How do I use a pivot table to get the data in a row as opposed to a column name? — John, Aug 31 '17 at 20:30
There are many answers like that on SO - I've used this one before: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Eli, Aug 31 '17 at 20:43
Looks like the same situation as this... https://stackoverflow.com/questions/45969364/tqsl-to-reformat-records-with-duplicate-data-into-one-record/45970267#45970267 — Jason A. Long, Aug 31 '17 at 23:29
@Eli - yes, there are numerous examples of pivot tables putting data in the COLUMN. But I need the data in the ROW. — John, Sep 01 '17 at 13:53
@Jason - thanks, I used your technique to achieve the desired results! — John, Sep 01 '17 at 14:35

score 1 · Accepted Answer · answered Sep 01 '17 at 14:47

Using Jason's technique I was able to produce the correct output.

SELECT M.RFQID, Requester, I.Line, ItemID, Qty1, Qty2, Qty3, VendorID
    FROM RFQ M
        JOIN Item I ON M.RFQID = I.RFQID 
        JOIN Vendor V ON I.Line = V.Line AND I.RFQID = V.RFQID
        JOIN (SELECT RFQID, LineItem, MAX(CASE WHEN QtyID = 1 THEN QUANTITY END) AS Qty1, MAX(CASE WHEN QtyID = 2 THEN QUANTITY END) AS Qty2, MAX(CASE WHEN QtyID = 3 THEN QUANTITY END) AS Qty3
                FROM ItemQuantity 
                GROUP BY RFQID, Line) IQ ON IQ.Line = I.Line AND IQ.RFQID = M.RFQID             
    WHERE ItemID IN ('P07', 'P08')
    ORDER BY M.RFQID DESC

Which results in

RFQID| Requestor | Line | ItemID | Quantity row1 | Quantity row2 | Quantity row3 | VendorID
555  |  789      |  1   |  P07   |  5            |  10           |  15           |  Speedy
777  |  789      |  1   |  P07   |  30           |  50           |  NULL         |  SlowPoke

Although my demo data did not include a QtyID the table can be rewritten as:

ItemQuantity table
----------------------------------------------
RFQID  |  Line  |  QtyID  |  Quantity  |  Unit
----------------------------------------------
555    |  1     |  1      |  5         |  In
555    |  1     |  2      |  10        |  In
555    |  1     |  3      |  15        |  In
777    |  1     |  1      |  30        |  In
777    |  1     |  2      |  50        |  In

The Unit can be added the same way.

Thanks Jason!

How can I get SQL results that resemble a pivot table

1 Answers1