Solved

MySQL Query to Create Ordered Table

Posted on 2014-02-12
12
407 Views
Last Modified: 2014-02-16
Hello Experts!

Not being a MySQL guru myself, I have a question which I hope one of you who is a guru at this can provide a solution for.

Let's say I have a table with information in the following format:

id     customer_no     key              value
1      1                        state            CA
2      1                        city              San Diego
3      1                        country       United States
4      2                        state            GA
5      2                        city              Atlanta
6      2                        country       United States
7      3                        state            NV
8      3                        city              Reno
9      3                        country       United States
...etc.

What sort of a query could produce the following table from the above information?

id     customer_no     city                 state            country
1      1                        San Diego       CA               United States
2      2                        Atlanta           GA               United States
3      3                        Reno               NV               United States
...etc.
0
Comment
Question by:OmniUnlimited
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 100 total points
Comment Utility
You'll have to select the values you require separately by linking to the table multiple times and putting the key you need in the "where" clause. So first you select the customer number and the city only, and give that select statement an alias. You join that to another select statement where you select only the customer number and the state, and finally you join that to a third select statement which only selects the customer and the country.
Like so:

Select		a.customer_no
	,	a.value as 'City'
	,	b.value	as 'State'
	,	c.value as 'Country'
from	(	select		customer_no
				,	[value]
			from	yourTable
			where	[KEY] = 'city') as a
	inner join 
	(	select		customer_no
				,	[value]
		from	yourTable
		where	[KEY] = 'state') as b
	on a.customer_no = b.customer_no
	inner join
	(	select		customer_no
				,	[value]
		from	yourTable
		where	[KEY] = 'country') as c
	on 	a.customer_no = c.customer_no
order by a.customer_no

Open in new window

0
 
LVL 17

Author Comment

by:OmniUnlimited
Comment Utility
Hi Kvwielink!  Thanks for your help.

As I mentioned, I'm not very good at this, so would you mind explaining to me the meaning of the brackets ([])?  (like [value] and [KEY], and shouldn't it be [key]?)
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Hi Omni,

I put the brackets around it to clearly identify them as column names. I actually typed this in MSSQL rather than MySQL and "value" and "key" are operators within MSSQL. I'm not sure if they are in MySQL as well. The brackets are optional most of the time, and you can probably remove them. You're right about key vs. KEY, but that's only an issue if your MySQL setup is case sensitive (MSSQL usually isn't, so I have a bad habit of ignoring it).
0
 
LVL 17

Author Comment

by:OmniUnlimited
Comment Utility
Awesome!  One last question:  what kind of effect would this query have on a really large table?  Would the fact that we are running three subqueries tend to slow things down a bit?  Do you know if this is the most efficient (or perhaps the only) way to do this?

Thanks again for all your help.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
This would depend a lot on the hardware used to run the database, whether the database is properly optimized/indexed, etc. I do believe this is the most logical way to do it though, not sure how you could make it any faster.
It's always better to run it first on a test database if you have one to see the effects. How many rows are there in the table you're looking to run this on?
0
 
LVL 17

Author Comment

by:OmniUnlimited
Comment Utility
Over 1,000,000.  I forgot to mention that MySQL field names are not case sensitive, but we are warned against mixing cases within the same statement.  I don't know if this would be the case if we used [KEY] vs. [key].
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
That's quite a bit. Why don't you run it over the the first 1000 records or so and see how that goes? If I'm not wrong MySQL would do that like this:

Select		a.customer_no
	,	a.value as 'City'
	,	b.value	as 'State'
	,	c.value as 'Country'
from	(	select		customer_no
				,	[value]
			from	yourTable
			where	[KEY] = 'city'
			limit 1000) as a
	inner join 
	(	select		customer_no
				,	[value]
		from	yourTable
		where	[KEY] = 'state') as b
	on a.customer_no = b.customer_no
	inner join
	(	select		customer_no
				,	[value]
		from	yourTable
		where	[KEY] = 'country') as c
	on 	a.customer_no = c.customer_no
order by a.customer_no

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
Comment Utility
Your table contains data in an non-normalised manner and the field `key` is being used to define the "virtual fields" city, state, country. Given that this appears to be very basic data about customers I would say the decision to use a  non-normalised data store design for this will definitely be sub-optimal for performance.

It may be very convenient for data capture to have the information in that manner, but it probably dooms you to many subqueries and re-joins throughout the application whenever "the customer" information is required.

An alternative method for simplifying that table as you require is as follows:
SELECT
      `customer_no`
    , min(CASE WHEN `key` = 'city'    THEN `value` END) AS `city`
    , min(CASE WHEN `key` = 'state'   THEN `value` END) AS `state`
    , min(CASE WHEN `key` = 'country' THEN `value` END) AS `country`
FROM Table1
GROUP BY
      `customer_no`
;

NB you could use max() instead of min() in the above

Open in new window

see this operating as http://sqlfiddle.com/#!9/43c87/2
0
 
LVL 2

Assisted Solution

by:c_kedar
c_kedar earned 100 total points
Comment Utility
Following is alternative, little concise solution:
select customer_no, 
	group_concat(if(key='state', value, null)) as state, 
	group_concat(if(key='city', value, null)) as city, 
	..... like wise for all keys
from t
group by 1;

Open in new window


This is uses MySQL-specific group_concat funcation.
Will be more manageable, and possibly efficient, if number of keys are high.
0
 
LVL 17

Author Comment

by:OmniUnlimited
Comment Utility
Hello Experts!

Many, many thanks for everyone chipping in to help on this question.

@PortletPaul:  Wow, kudos for the very unique (and efficient!) solution!  I never would have thought of using min() or max() and CASE like you did.  Excellent!

@Kvwielink:  I did try your solution on the 1000 records as you suggested, and for me the query was agonizingly slow.  Multiply that by the over 1000 times you would need to do the whole table and I'm afraid that this will simply take too much time.  For kicks, I slapped your query into the SQLFiddle supplied by PortletPaul.  His code ran in 163ms.  Yours took 232ms.

@c_kedar:  I tried the following query in the SQLFiddle supplied by PortletPaul as well:
select customer_no, 
	group_concat(if(`key`='state', value, null)) as state, 
	group_concat(if(`key`='city', value, null)) as city, 
	group_concat(if(`key`='country', value, null)) as country 
from Table1

Open in new window

and yours took 288ms.
0
 
LVL 17

Author Closing Comment

by:OmniUnlimited
Comment Utility
Many thanks again Experts!  I learned something today.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
It's a distinct pleasure to help, thanks for the question.

Cheers. Paul
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now