Have results split into two colums

[Number11]

Hi,
Looking for some help i need to split the results of file Pick_Bin into two separate columns where the order ID is differnt

Table fields are:

Customer_ID
Order_Date
Order_No
Item_Code
Pick_Bin

Looking to get output like this.. so where the Pick_B results are 1 or 2 split into new columns

Customer_ID	Order_Date	Order_No	Item_Code	Pick_Bin_1	Pick_Bin_2
A14212	19/06/2025	111	444242	5	9
A55545	19/06/2025	222	24242	4	5
A87788	19/06/2025	1145	4242	1	3

​

 

[Gasman]

And what are the rules for doing that?
What do they look like before the split?

 

[Number11]

And what are the rules for doing that?

Customer_ID Order_Date Order_No Item_Code Pick_Bin
A14212 19/06/2025 111 444242 5
A14212 19/06/2025 111 444242 9
A55545 19/06/2025 222 24242 4
A55545 19/06/2025 222 24242 5
A87788 19/06/2025 1145 4242 1
A87788 19/06/2025 1145 4242 3

 

[Gasman]

So what happens if there are 3 records?
Look at using a crosstab.

 

[MajP]

You would have to number the records first using a ranking query and then use a crosstab
Rank
A14212 19/06/2025 111 444242 5 --- 1
A14212 19/06/2025 111 444242 9 --- 2
A55545 19/06/2025 222 24242 4 --- 1
A55545 19/06/2025 222 24242 5 --- 2
A87788 19/06/2025 1145 4242 1 --- 1
A87788 19/06/2025 1145 4242 3 --- 2

There are many different ways to do a ranking query you can google

 

[Number11]

So what happens if there are 3 records?
Look at using a crosstab.

Would only ever be max of 2

 

[MajP]

Regardless if 2 or more pick bins.

ID	CustomerID	Order_No	Item_Code	Pick_Bin
1	A	1	111	5
2	A	1	111	9
3	B	2	222	4
4	B	2	222	5
5	C	3	333	1
6	C	3	333	3
7	C	3	333	5

SalesData

Need to rank the bins used as 1 and 2, 3, ...

SELECT *
FROM (SELECT A.CustomerID, A.Order_No, A.Item_Code, A.Pick_Bin, "BIN " & Count(*) AS Rank
FROM SalesData as A INNER JOIN SalesData AS B ON A.Order_No = B.Order_No
And A.Pick_Bin>=B.Pick_Bin
GROUP By A.CustomerID
,A.Order_No
,A.Item_Code
,A.Pick_Bin
)  AS RankingQuery
ORDER BY A.CustomerID, A.Order_No, Rank;

CustomerID	Order_No	Item_Code	Pick_Bin	Rank
A	1	111	5	BIN 1
A	1	111	9	BIN 2
B	2	222	4	BIN 1
B	2	222	5	BIN 2
C	3	333	1	BIN 1
C	3	333	3	BIN 2
C	3	333	5	BIN 3

qryRank

Then make the cross tab

CustomerID	Order_No	Item_Code	BIN 1	BIN 2	BIN 3
A	1	111	5	9
B	2	222	4	5
C	3	333	1	3	5

qryRank_Crosstab

 

[CJ_London]

To be clear you have a single text field per row with values separated by a single space?

Your example data seems too simplistic. What identifies the pair? Customer? Order? Item? A combination of 2 or more?

Can you only have one row?

 

[MajP]

The demo is simplistic.
My assumption is that you are putting items in multiple bins
for a given customer, for a given order, and for a given item

The query would fail if you are putting multple items-codes in a bin, or multiple order, or multiple customer order items.

 

[plog]

Would only ever be max of 2

Ha ha. Assumptive 'woulds' and 'shoulds' always puts a smile on my face.

Not saying you are wrong, but in my experience data reality very rarely matches peoples assumptions about their data. And very often to poor results.

SELECT Customer_ID, Order_Date, Order_No, Item_Code
FROM YourTableNameHere
GROUP BY Customer_ID, Order_Date, Order_No, Item_Code
HAVING COUNT(Customer_ID)>2

Just for fun, run that query on your data and tell me if you get any results. If you do, turns out your assumptions are incorrect.

 

[MajP]

Not saying you are wrong, but in my experience data reality very rarely matches peoples assumptions about their data. And very often to poor results.

But as I demoed with 3 bins for Customer C it does not matter if the assumption is wrong and they have more than 2 bins.

 

[Number11]

Regardless if 2 or more pick bins.

ID	CustomerID	Order_No	Item_Code	Pick_Bin
1	A	1	111	5
2	A	1	111	9
3	B	2	222	4
4	B	2	222	5
5	C	3	333	1
6	C	3	333	3
7	C	3	333	5

SalesData

Need to rank the bins used as 1 and 2, 3, ...

SELECT *
FROM (SELECT A.CustomerID, A.Order_No, A.Item_Code, A.Pick_Bin, "BIN " & Count(*) AS Rank
FROM SalesData as A INNER JOIN SalesData AS B ON A.Order_No = B.Order_No
And A.Pick_Bin>=B.Pick_Bin
GROUP By A.CustomerID
,A.Order_No
,A.Item_Code
,A.Pick_Bin
)  AS RankingQuery
ORDER BY A.CustomerID, A.Order_No, Rank;

CustomerID	Order_No	Item_Code	Pick_Bin	Rank
A	1	111	5	BIN 1
A	1	111	9	BIN 2
B	2	222	4	BIN 1
B	2	222	5	BIN 2
C	3	333	1	BIN 1
C	3	333	3	BIN 2
C	3	333	5	BIN 3

qryRank

Then make the cross tab

CustomerID	Order_No	Item_Code	BIN 1	BIN 2	BIN 3
A	1	111	5	9
B	2	222	4	5
C	3	333	1	3	5

qryRank_Crosstab

Thanks for your help stuck on the qryRank_Crosstab now

 

[MajP]

Thanks for your help stuck on the qryRank_Crosstab now

If you get qryRank then using the wizard
row headings, Customer, Order, ItemCode
Column heading Rank
Data is First of Pick_Bin

First works because you only have one value in each row column

 

[Pat Hartman]

Would only ever be max of 2

Until there are 3
Don't hard code things you don't need to hard code. Once you may have more then one of something, you have many and that means you need to look ahead to how the business may grow. Don't let unnecessary and therefore silly decisions like this one come back to bite you in the b later. Better to never have boxed yourself into such a problem.

@MajP gave you a good solution that doesn't limit you to only two bins.