Solved

MySQL Query to Create Ordered Table

Posted on 2014-02-12
12
418 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
[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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 100 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
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.

 
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 300 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 100 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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