[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

changing "vertical" data to "horizontal" data

Posted on 2012-04-11
15
Medium Priority
?
524 Views
Last Modified: 2012-07-07
I have a recurring problem that is difficult for me to explain, but here it is:
As an example, I have a database with one table that contains house addresses and another table that contains owners' names, usually more than one per address.
I want to convert this to one table, with the address in one field and the owners' names in subsequent fields, all in one record.
In the past, I've managed to do this (in most cases), but it involves a lot of manipulation of the data and setting up multiple queries, etc.  It often takes a long time.
Is there a common/easier way to do this that anyone knows of?  I typically use MS Access, Crystal Reports, and/or Excel to do my data gathering.
Hope this makes sense.
Thanks, Experts!!!
0
Comment
Question by:Nero83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 37834922
YOu need to build a query that gatherrs the data you want from the tables.  That view can then be used to update or create the new table.

If you had 2 tables like
Names and Addresses

Names
   PersonKey
   FirstName
   LastName

Addresses
   AddressKey
   fkPersonKey
   AddressLine1
   AddressLine2
   AddressLine3
   City
   State
   Zip

A query like

SELECT Names.FirstName, Names.LastName, Addresses.AddressLine1, Addresses.AddressLine2, Addresses.AddressLine3, Address.City, Address.State, Address.Zip
FROM Names INNER JOIN Addrresses
ON Names.PersonKey = Address.fkPersonKey

If a Name can be exist without an address then use LEFT OUTER JOIN

mlmcc
0
 

Author Comment

by:Nero83
ID: 37835008
Understood, thanks.
However, in my situation, the Names table has an address key.  There are multiple names per address, and I am looking to have each of those names in it's own field called, say, Name1, Name2...

Addkey      Address
12345        999 Main St.

fkAddkey   name
12345         joe smith
12345         mary smith

and i want it to come out:

key        address              name1          name2
12345   999 Main St.       joe smith      mary smith

does this make sense?  In some cases I don't even have the names numbered in their table to differentiate them in any way.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37835077
Is there a limit to the names at an address?

mlmcc
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Nero83
ID: 37835373
Well, it can be limtless in terms of the database but in reality it rarely exceeds six, and it's acceptable for me to artifically limit it to, say, four names.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37835485
If you "Convert" this to the one row format you are specifying, you will have an un-normalized structure.

Is this conversion for reporting or some other specific purpose?
Perhaps you could explain why you need this, in case the same result can be obtained in a different way?

The data should really remain in the current structure IMHO
0
 

Author Comment

by:Nero83
ID: 37835614
@boag2000 this is usually for delivering data for mailings/reporting.  I'm not sure what un-normalized means, but it's not for further database use or anything - I send off this data and never use it again.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37835644
What format do you need it in?
A CSV, text file, a printed report, Excel?

It would be relatively easy in a report like Crystal

mlmcc
0
 

Author Comment

by:Nero83
ID: 37835664
My current need is going to excel but it would be nicest for me to have a method in Crystal so I could run it anytime and export it...I use Crystal often.  Any method would be great!
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 37835852
Check this report

mlmcc
Q-27671572.rpt
Q-27671572.mdb
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 37838808
See my Access Archon articles on creating a denormalized table :

http://www.helenfeddema.com/Files/accarch43.zip
http://www.helenfeddema.com/Files/accarch125.zip
0
 

Author Comment

by:Nero83
ID: 37838910
@mlmcc: Thanks!  That's an interesting solution.  
I was looking for the names being put to their own fields, but I suppose I can use the formulas you provided to insert delimeters that I can export and reimport to separate into fields...?
0
 

Author Comment

by:Nero83
ID: 38149863
I've requested that this question be closed as follows:

Accepted answer: 0 points for Nero83's comment #37838910

for the following reason:

it was an ok workaround, but not a solution exactly.  appreciated the effort, though.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38149864
Workarounds can be accepted as the solution or perhaps we should continue this discussion and reach a real solution

mlmcc
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question