Separating row data out into columns

Question

I have a customers table that houses contacts. Each contact is added as another row to that customer.

I want to create my query so that I can create columns for contact1, contact2, contact3 and so forth. What's the best way to do this?

My rows are dynamic and I have been looking into unpivot and pivot but I'm not sure how I would build that into my query as I have a few joins and other data in the query.

SQL Fiddle example

You are trying to do something wrong, creating a column for each row ?! — BeS, Feb 23 '15 at 15:24
if you want a single column with a list of values -- that is actually easier then making columns... unless you know exactly how many columns. — Hogan, Feb 23 '15 at 16:26
why is your fiddle in mySQL when this question is tagged sqlserver? — Hogan, Feb 23 '15 at 16:29
@Bes - Rows are duplicated since each contact is associated with the parent account. but I want to delete the repeat rows and move the contact data into seperate columns (contact1, contactphone1, contact2, contactphone2) — Matthew Jakachira, Feb 23 '15 at 17:20
@Charlie - No there is no set number of contacts per account — Matthew Jakachira, Feb 23 '15 at 17:21

score 0 · Answer 1 · edited May 23 '17 at 11:53

0

Dynamic pivot query is what you need. See this: http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

and this: SQL Server dynamic PIVOT query?

edited May 23 '17 at 11:53

Community

1
1

answered Feb 23 '15 at 16:24

Bestia Immunda

16
3

Separating row data out into columns

1 Answers1