SQL : Output multiple left joined rows as one record for csv file

Question

I have 3 tables :

Stock - ID, Description, Quantity etc
Equipment - ID, Description
LinkStockEquipment - StockID, EquipmentID, Quantity

There may be multiple types of equipment required for each stock item.

I want to output all the data into a csv file for the customer.

Ideally they want:
StockID, StockDescription, StockQuantity, Equipment1, Equipment1Qty, Equipment2, Equipment2Qty, Equipment3, Equipment3Qty etc

So my starting point is:

SELECT * FROM Stock 
LEFT JOIN LinkStockEquipment ON LinkStockEquipment.StockID = Stock.ID 
LEFT JOIN Equipment ON Equipment.ID = LinkStockEquipment.EquipmentID

But of course this just gives me multiple rows when there are multiple types of equipment per stock item. I could just take the top or bottom rows using a SORT and a MAX or MIN but how would I then access the remaining rows, one by one to link them in.

Could it be something like (syntax wrong but trying to get message across)

SELECT * FROM Stock 
LEFT JOIN LinkStockEquipment AS LSE1 ON LSE1.StockID = Stock.ID LIMIT 1
LEFT JOIN LinkStockEquipment AS LSE2 ON LSE2.StockID = Stock.ID AND LSE1.StockID <> LSE2.StockID LIMIT 1
LEFT JOIN LinkStockEquipment AS LSE3 ON LSE3.StockID = Stock.ID AND LSE1.StockID <> LSE2.StockID AND LSE2.StockID <> LSE3.StockID LIMIT 1
LEFT JOIN Equipment ON Equipment.ID = LinkStockEquipment.EquipmentID

Apologies if this has already been answered directly, I've only used basic SQL before so I have not fully understood the other answers to similar questions.

MySQL Left Join Many to One Row might do what I want but I also need the quantity of each type of equiment so it didn't seem to be the solution.

Update 1: I have also looked at MySQL pivot row into dynamic number of columns however I didn't see how it could apply when I need both equipment and quantity each time, rather than just one item to pivot on. I'll read up more on Pivoting.

Update 2: Reading about pivoting it would give me a separate column for each equipment type but there could be 100 different equipment types. However each stock item might only have 2 or 3 out of those 100 different types. I only therefore want to list what those equipment types are not a column for each.

Given:

Stock ID | Equipment ID | Equipment Qty
---------------------------------------
A1       | E1           | 5
A1       | E2           | 3
A2       | E3           | 4
A2       | E4           | 6
A3       | E5           | 2

I would like

Stock ID | Eqpmnt ID 1 | Eqpmnt Qty 1 | Eqpmnt ID 2 | Eqpmnt Qty 2
------------------------------------------------------------------
A1       | E1          | 5            | E2          | 3
A2       | E3          | 4            | E4          | 6
A3       | E5          | 2            |             |

Not

Stock ID | E1 | E2 | E3 | E4 | E5 
------------------------------------------------------------------
A1       | 5  | 3  |    |    |
A2       |    |    | 4  | 6  |
A3       |    |    |    |    | 2

Unless I have misunderstood pivoting?

Update and Result: For some reason I cannot set this as the answer to the question, however in case this helps anyone else ...

Thanks to Juan below, I added an additional row into my LinkStockEquipment table using info from this question : mysql increment value based on previous record

UPDATE t1
SET t1.Counter = t2.Counter
FROM  LinkStockEquipment t1
LEFT JOIN
(
    SELECT
    t.EquipmentID,
    t.StockID ,
    (SELECT COUNT(*) FROM [LinkStockEquipment] AS x WHERE x.EquipmentID <= t.EquipmentID
    AND x.StockID = t.StockID) AS Counter
    FROM [LinkStockEquipment] t
) as t2
    ON t1.EquipmentID= t2.EquipmentID AND t1.StockID = t2.StockID

So then the table looks like:

Stock ID | Equipment ID | Equipment Qty | Counter
-------------------------------------------------
A1       | E1           | 5             | 1
A1       | E2           | 3             | 2
A2       | E3           | 4             | 1
A2       | E4           | 6             | 2
A3       | E5           | 2             | 1

Now I can pivot, so using

SELECT P.StockID, P.[1], L1.Qty, P.[2], L2.Qty, P.[3], L3.Qty
FROM (SELECT StockID, EquipmentID, Counter
FROM [LinkStockEquipment]) AS D
PIVOT(MAX(EquipmentID) FOR Counter IN([1],[2],[3])) AS P
LEFT JOIN [LinkStockEquipment] L1 ON L1.StockID = P.StockID
AND L1.EquipmentID = P.[1]
LEFT JOIN [LinkStockEquipment] L2 ON L2.StockID = P.StockID 
AND L2.EquipmentID = P.[2]
LEFT JOIN [LinkStockEquipment] L3 ON L3.StockID = P.StockID 
AND L3.EquipmentID = P.[3]

I can left join in the Stock table details and I have the desired result.

@JuanCarlosOropeza Thanks for the reply, I've had a quick read about pivoting and I've updated my question. I'm not sure Pivoting is what I need unless I have misunderstood. — rbassett, Dec 10 '18 at 20:44
Yes, that is exactly what you need. First you need to create `Eqpmnt ID 1` as a row value and then you pivot over that — Juan Carlos Oropeza, Dec 10 '18 at 20:56
Looks like you solve it? And wasnt yesterday the tag MySql ? — Juan Carlos Oropeza, Dec 11 '18 at 13:53
Yes I did, and yes it was, I need it for both - old database is in MS SQL and new database is in MySql. So I was getting a little messed up with syntax trying to solve both at once. I've just focused on MS SQL now and will figure out MySql later. Thanks for your help. — rbassett, Dec 11 '18 at 14:06
For sql server 2008+ you can use row_number instead https://rextester.com/BNTET26013. New version of MySql also have row_number now — Juan Carlos Oropeza, Dec 11 '18 at 14:11

Juan Carlos Oropeza · Accepted Answer · 2018-12-11T15:52:42.007

If you have a fixed number of equipment for stock (I assume 3 for this )

You can do a pivot using GROUP BY.

SQL DEMO

WITH tmpResult as (
    SELECT 
         [Stock ID],
         'Equipment ID ' + CAST(rn AS VARCHAR(16)) as lblEq,
         [Equipment ID],
         [Equipment Qty]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows
)    
SELECT [Stock ID], 
       MAX( CASE WHEN lblEq = 'Equipment ID 1' THEN [Equipment ID]  END) as [Eqp ID 1],
       MAX( CASE WHEN lblEq = 'Equipment ID 1' THEN [Equipment Qty] END) as [Eqp Qty 1],
       MAX( CASE WHEN lblEq = 'Equipment ID 2' THEN [Equipment ID]  END) as [Eqp ID 2],
       MAX( CASE WHEN lblEq = 'Equipment ID 2' THEN [Equipment Qty] END) as [Eqp Qty 2],
       MAX( CASE WHEN lblEq = 'Equipment ID 3' THEN [Equipment ID]  END) as [Eqp ID 3],
       MAX( CASE WHEN lblEq = 'Equipment ID 3' THEN [Equipment Qty] END) as [Eqp Qty 3]
FROM       tmpResult
GROUP BY [Stock ID];

OUTPUT

Now if you want use PIVOT the important part is on the data preparation. In this case I have to convert qty to string. Again you need to know the number of fields you want to pivot

SQL DEMO

WITH tmpResult as (
    SELECT 
         [Stock ID],
         'Eqp ID ' + CAST(rn AS VARCHAR(16)) as label,
         [Equipment ID] as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows

    UNION ALL

    SELECT 
         [Stock ID],
         'Eqp Qty ' + CAST(rn AS VARCHAR(16)) as label,
         CAST([Equipment Qty] AS VARCHAR(16))  as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows

)    
SELECT [Stock ID], 
       [Eqp ID 1], [Eqp Qty 1],
       [Eqp ID 2], [Eqp Qty 2]       
FROM   ( SELECT * FROM tmpResult ) as x
PIVOT ( 
        max( [Value] ) 
        for label in  ( [Eqp ID 1], [Eqp Qty 1], [Eqp ID 2], [Eqp Qty 2] )
      ) as pvt

OUTPUT

Now if you dont know how many equipment you have, then you need dynamic PIVOT.

SQL DEMO

First you need a temporal table.

SELECT 
     [Stock ID],
     [label],
     [Value]
INTO tmpResult
FROM (
    SELECT 
         [Stock ID],
         'Eqp ID ' + CAST(rn AS VARCHAR(16)) as label,
         [Equipment ID] as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows

    UNION ALL

    SELECT 
         [Stock ID],
         'Eqp Qty ' + CAST(rn AS VARCHAR(16)) as label,
         CAST([Equipment Qty] AS VARCHAR(16))  as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows
    ) as x;

Then you need prepare the pivot query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.label)
            FROM tmpResult c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @cols; 

set @query = 'SELECT [Stock ID], ' + @cols + ' FROM  
            (
                SELECT *
                FROM tmpResult
           ) x
            pivot 
            (
                 max(Value)
                for label in (' + @cols + ')
            ) p '

execute(@query);

OUTPUT

Here the problem is the column order. I will try to see if can fix it.

SQL : Output multiple left joined rows as one record for csv file

1 Answers1