MySQL Query to Create Ordered Table

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.
LVL 17
OmniUnlimitedAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
Koen Van WielinkConnect With a Mentor Business Intelligence SpecialistCommented:
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
 
OmniUnlimitedAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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
 
OmniUnlimitedAuthor Commented:
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
 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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
 
OmniUnlimitedAuthor Commented:
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
 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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
 
c_kedarConnect With a Mentor Commented:
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
 
OmniUnlimitedAuthor Commented:
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
 
OmniUnlimitedAuthor Commented:
Many thanks again Experts!  I learned something today.
0
 
PortletPaulfreelancerCommented:
It's a distinct pleasure to help, thanks for the question.

Cheers. Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.