I have my main table, named tblMain. Here is a sample data set from that table:
pk itemID stockID status
608 24538784 3833 -
609 24538784 3881 remain
610 24538784 5673 -
633 24538784 4605 +
634 24538784 105 +
617 24540467 105 remain
618 24540467 3881 remain
619 24540467 5673 -
624 24704802 866 remain
625 24704802 5327 remain
626 24704802 5673 -
636 24704802 105 +
I would like to "pivot" (almost...) this data so it's grouped by itemID with the status pre-pending the stockID. I am also ignoring the entries with status = "remain"
This is the output i get from my query now:
itemID stockID stockID2 stockID3 stockID4 stockID5 stockID6
24538784 - 3833 - 3881 - 5673
24538784 + 4605 +105
24540467 - 5673
24704802 - 5673
24704802 +105
As you can see, it is seperating and grouping the entries if they contain " - " or " + ". I want them in the same record. Thus, I would like the output from the query to be:
itemID stockID stockID2 stockID3 stockID4 stockID5 stockID6
24538784 - 3833 - 3881 - 5673 + 4605 + 105
24540467 - 5673
24704802 - 5673 + 105
Here is my query. How can i change this to achieve my required format?
SELECT x.itemID, status+First(x.stockID1) AS stockID1, status+First(x.stockID2) AS stockID2, status+First(x.stockID3) AS stockID3, status+First(x.stockID4) AS stockID4, status+First(x.stockID5) AS stockID5, status+First(x.stockID6) AS stockID6
FROM [SELECT status,a.itemID, a.stockID AS stockID1,
(Select First(b.stockID) from tblMain b where b.itemID=a.itemID and b.PK = a.PK +1) AS stockID2,
(Select First(c.stockID) from tblMain c where c.itemID=a.itemID and c.Pk = a.PK +2) AS stockID3,
(Select First(d.stockID) from tblMain d where d.itemID=a.itemID and d.Pk = a.PK +3) AS stockID4,
(Select First(e.stockID) from tblMain e where e.itemID=a.itemID and e.Pk = a.PK +4) AS stockID5,
(Select First(f.stockID) from tblMain f where f.itemID=a.itemID and f.Pk = a.PK +5) AS stockID6
FROM tblMain AS a]. AS x
WHERE status<> 'remain'
GROUP BY x.itemID, status;
Many thanks
