?
Solved

MySQL Query to Create Ordered Table

Posted on 2014-02-12
12
Medium Priority
?
421 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 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 400 total points
ID: 39855269
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
ID: 39855349
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 13

Expert Comment

by:Koen Van Wielink
ID: 39855352
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 17

Author Comment

by:OmniUnlimited
ID: 39855354
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 13

Expert Comment

by:Koen Van Wielink
ID: 39855360
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
ID: 39855362
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
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39855380
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 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 39855396
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 400 total points
ID: 39855407
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
ID: 39856377
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
ID: 39856394
Many thanks again Experts!  I learned something today.
0
 
LVL 49

Expert Comment

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

Cheers. Paul
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

864 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