SQL Server – Combining Multiple Rows with Arbitrary Number of Columns

sql server

I am attempting to format a query but, I don't think I know all the required methods to do what I want currently I have these results

Source
------
item    |PackQty|TotalUsage |JobDescription|job_usage   
STE0295 |5      |20         |41721-2       |1
STE0295 |5      |20         |41925-1       |1
STE0295 |5      |20         |41949-1       |1
STE0295 |5      |20         |41997-1       |1

and I want to combine all item keys into a single row like this

STE0295 |5      |20         |41721-2       |1   |41925-1    |1  |41949-1    |1  |41997-1    |1

These results are not the entire set; there are thousand of unique 'item' records and they appear in arbitrary number of rows based on the different job Description. I have used pivots before and also the stuff for xml method but, I feel like I am missing some concepts to achieve this format. Here is the query so far

    select  tv.Item,
       --tv.ItemDescription,
       tv.PackQty,
       TotalUsage = (
                        SELECT sum(tvss.Qty * -1)
                        FROM transactions_VIEW as tvss
                        WHERE tvss.Item = tv.Item
                          AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                          AND tvss.Supplier IN ('###', '###', '###')
                          AND tvss.TranType IN ('I','B')
                    ),
       tv.JobDescription,
    count(*) as job_usage
from transactions_VIEW as tv
         join Job as j on tv.JobNumber = j.MyNo
WHERE j.Active = 1
  AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
  AND tv.Supplier IN ('###', '###','###')
  AND tv.TranType IN ('I', 'B')
group by tv.Item, tv.JobDescription, tv.ItemDescription, Qty, PackQty

I have looked around but, I don't know the name of the technique to use so I am floundering. Any pointers in the right directions would be awesome.

Best Answer

well it looks like I will use stuff xml again and parse out in application. however if any one knows any clever rearrangement of this query using cte's and cross or outer applys I would love to hear it. 

        select distinct tv.Item,
       --tv.ItemDescription,
       tv.PackQty,
       TotalUsage = (
                        SELECT sum(tvss.Qty * -1)
                        FROM transactions_VIEW as tvss
                        WHERE tvss.Item = tv.Item
                          AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                          AND tvss.Supplier IN ('###', '###', '###')
                          AND tvss.TranType IN ('I','B')
                    ),
       [jobDesc:use] = STUFF((SELECT ';' + CAST(tvs.JobDescription as nvarchar(max)) +':'+CAST(count(*) as nvarchar(max))
                          from transactions_VIEW as tvs
                                   join Job as js on tvs.JobNumber = js.MyNo
                          WHERE js.Active = 1
                            AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                            AND tvs.Supplier IN ('###', '###','###')
                            AND tvs.TranType IN ('I', 'B')
                            AND tvs.Item = tv.Item
                          Group By tvs.JobDescription FOR XML Path ('')), 1, 1, '')
from transactions_VIEW as tv
         join Job as j on tv.JobNumber = j.MyNo
WHERE j.Active = 1
  AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
  AND tv.Supplier IN ('###', '###','###')
  AND tv.TranType IN ('I', 'B')
group by tv.Item, tv.JobDescription, tv.ItemDescription, Qty, PackQty
Related Question