November 13, 2013 at 10:48 am
So I am having some issues trying to capture the different payment methods by order number.
So and order can be divided up into different pay methods - they could split an order up and make a partial payment by VISA, AMEX, MC, giftcard.
I have some test data in a table.
order_numbersequnce_numpaytype
10185 2 VISA
10185 2 AMEX
10185 1 MC
10187 1 MC
10187 1 GIFTCARD
10188 1 GIFTCARD
10188 1 VISA
10188 1 MC
10188 1 AMEX
create table order_pay_types
(order_number int,sequnce_num int,paytype varchar(25))
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10185,2,'VISA')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10185,2,'AMEX')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10185,1,'MC')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10187,1,'MC')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10187,1,'GIFTCARD')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10188,1,'GIFTCARD')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10188,1,'VISA')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10188,1,'MC')
INSERT INTO order_pay_types(order_number,sequnce_num,paytype)
VALUES(10188,1,'AMEX')
GO
I would like to transpose the data so that it comes in the following format - group by order_number and sequence_num
Order_Number Sequence_num paytype1 paytype2 paytype3 paytype4
10185 2 VISA AMEX
10185 1 MC
10187 1 MC GIFTCARD
10188 1 GIFTCARD VISA MC AMEX
Any suggestions on how I can easily do that? I have looked at pivot and unpivot - but haven't been able to make sense of it.
November 13, 2013 at 11:24 am
So I figured out one solution - which is to go off of the rownumber.
SELECT distinct Order_number, sequnce_num,
--get the special handling codes and show them as columns, max of 3 (agreed by users)
(SELECT paytype
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY paytype) AS ROWNUMBER,paytype
FROM order_pay_types
WHERE order_pay_types.Order_Number = t1.Order_Number and order_pay_types.sequnce_num = t1.sequnce_num
) pay_method
WHERE ROWNUMBER = 1) pay_method1,
(SELECT paytype
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY paytype) AS ROWNUMBER,paytype
FROM order_pay_types
WHERE order_pay_types.Order_Number = t1.Order_Number and order_pay_types.sequnce_num = t1.sequnce_num
) HandlingCode
WHERE ROWNUMBER = 2) pay_method2,
(SELECT paytype
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY paytype) AS ROWNUMBER,paytype
FROM order_pay_types
WHERE order_pay_types.Order_Number = t1.Order_Number and order_pay_types.sequnce_num = t1.sequnce_num
) HandlingCode
WHERE ROWNUMBER = 3) pay_method3,
(SELECT paytype
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY paytype) AS ROWNUMBER,paytype
FROM order_pay_types
WHERE order_pay_types.Order_Number = t1.Order_Number and order_pay_types.sequnce_num = t1.sequnce_num
) HandlingCode
WHERE ROWNUMBER = 4) pay_method4
FROM order_pay_types t1
Also please ignore the misspelling of sequence_number - I left out the 'e'.
I would still love to see any other suggestions on how to pull this.
November 13, 2013 at 11:57 am
I misread the expected results, I will post a correct solution in a few minutes.
A simpler approach can be found on this article:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Here's an example on your data:
SELECT order_number,
sequnce_num,
STUFF( (SELECT ' ' + paytype
FROM order_pay_types x
WHERE x.order_number = o.order_number
AND x.sequnce_num = o.sequnce_num
FOR XML PATH('')), 1, 1,'')
FROM order_pay_types o
GROUP BY order_number,
sequnce_num
ORDER BY order_number,
sequnce_num DESC
I really recommend you to read the article to understand what the code does.
November 13, 2013 at 12:05 pm
Now, there's a method to obtain the correct result. It's called CROSS TABS and you can find information on the following articles:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY order_number, sequnce_num ORDER BY (SELECT NULL)) rn
FROM order_pay_types
)
SELECT order_number,
sequnce_num,
MAX( CASE WHEN rn = 1 THEN paytype ELSE '' END) AS paytype1,
MAX( CASE WHEN rn = 2 THEN paytype ELSE '' END) AS paytype2,
MAX( CASE WHEN rn = 3 THEN paytype ELSE '' END) AS paytype3,
MAX( CASE WHEN rn = 4 THEN paytype ELSE '' END) AS paytype4
FROM CTE
GROUP BY order_number,
sequnce_num
ORDER BY order_number,
sequnce_num DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply