Creating Table for Pivot Table

Question

Creating Table for Pivot Table

marySQL

SSC Journeyman

Points: 96
More actions
June 18, 2013 at 10:33 am

#279285

Hello,
I need to create a pivot table, and I think I need a new column to get the pivot to work. Basically, what I have is data with customer#, customername and dist. Each customer can have more than 1 distributor, and I want my data to be arranged like: customer#, customername, dist1, dist2, dist3, etc (as many as they have).
I think I need to add a number column that will count each dist for each customer. But how do I do this?
Thanks!
Mary

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you know there is a maximum number of distributores, it's pretty easy; for example, this would assume a max of five distributora records:

WITH CTE AS (

SELECT customer#, customername,

ROW_NUMBER() OVER(PARTITION BY customer# ORDER BY dist) AS rn

FROM MyTable)

SELECT customer#,

MAX(CASE WHEN rn=1 THEN dist END) AS dist1,

MAX(CASE WHEN rn=2 THEN dist END) AS dist2,

MAX(CASE WHEN rn=3 THEN dist END) AS dist3,

MAX(CASE WHEN rn=4 THEN dist END) AS dist4,

MAX(CASE WHEN rn=5 THEN dist END) AS dist5

FROM CTE

GROUP BY customer#

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

marySQL SSC Journeyman Points: 96 More actions · Answer 2

Thank you. Luckily for me it turns out the highest # of distributors used is 17, so I can work with this.

Thanks for your help!!

Mary

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

For more information, I recommend you to read the following articles that would even allow you to return a variable number of columns.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

marySQL SSC Journeyman Points: 96 More actions · Answer 4

marySQL

SSC Journeyman

Points: 96

June 18, 2013 at 4:15 pm

#1625246

Thanks!