February 20, 2012 at 10:31 pm
We are using SQL 2000
I need help with the following situation.
I need to pivot some data. I have searched the forum and while there are lots of posts about how to pivot, none of them seem to do what I need.
So here goes.
I know that everyone recomends that the client do the pivoting, but in this case, this is a data extract that is going into a different system.
I need all the po_numbers in individual columns in one row.
The external system will only handle 5 columns of po numbers per customer.
-- Desired Results
customer_id,po_number1, po_number2, po_number3, po_number4, po_Number5
178976, SC30134526,SC30077506,6092_MFGP00060008,30078311,00027591
289652, S00018020, S18706, S30123519
465893, C30047899, C30056967, C30046376, C30072186, C30078250
I have a table called customer_po
There are two columns
Customer_id and po_Number
-- Table creation
Create table customer_po
(
customer_id int -- foriegn Key to the customer table. A one-many relationship
po_number varchar(50)
)
-- Sample Data
insert into customer_po (customer_id, po_Number)
values (178976, 'SC30134526')
insert into customer_po (customer_id, po_Number)
values (178976, 'SC30077506')
insert into customer_po (customer_id, po_Number)
values (178976, '6092_MFGP00060008')
insert into customer_po (customer_id, po_Number)
values (178976, '30078311')
insert into customer_po (customer_id, po_Number)
values (178976, '00027591')
insert into customer_po (customer_id, po_Number)
values (178976, 'F30042647')
insert into customer_po (customer_id, po_Number)
values (289652, 'S00018020')
insert into customer_po (customer_id, po_Number)
values (289652, 'S18706')
insert into customer_po (customer_id, po_Number)
values (289652, 'S30123519')
insert into customer_po (customer_id, po_Number)
values (465893, 'C30047899')
insert into customer_po (customer_id, po_Number)
values (465893, 'C30056967')
insert into customer_po (customer_id, po_Number)
values (465893, 'C30046376')
insert into customer_po (customer_id, po_Number)
values (465893, 'C30072186')
insert into customer_po (customer_id, po_Number)
values (465893, 'C30078250')
I appreciate the help.
Thanks
Gary
February 21, 2012 at 4:28 am
You haven't said how you determine which 5 to keep. In your sample data, "178976" has 6 "po_Number".
Assuming you're using SQL Server 2000, here's one way to do it: -
SELECT customer_id,
MAX(CASE WHEN rn = 1 THEN po_Number ELSE NULL END) AS po_number1,
MAX(CASE WHEN rn = 2 THEN po_Number ELSE NULL END) AS po_number2,
MAX(CASE WHEN rn = 3 THEN po_Number ELSE NULL END) AS po_number3,
MAX(CASE WHEN rn = 4 THEN po_Number ELSE NULL END) AS po_number4,
MAX(CASE WHEN rn = 5 THEN po_Number ELSE NULL END) AS po_number5
FROM (SELECT customer_id, po_Number,
(SELECT COUNT(*)
FROM customer_po counter
WHERE counter.customer_id = customer_po.customer_id
AND counter.po_Number <= customer_po.po_Number) AS rn
FROM customer_po) innerQ
GROUP BY customer_id
February 21, 2012 at 7:20 pm
Cadavre,
Thank you for your reply.
This got me pointed in the right direction.
Thanks again.
Gary
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply