Solved

MySQL Query to Create Ordered Table

Posted on 2014-02-12
12
409 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 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
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 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 48

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 48

Expert Comment

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

Cheers. Paul
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Please help for the below sql query. 1 24
MS SQL 2008 Divide by zero error encountered. Error 3 33
Create a MySQL table as easily as possible 6 35
mysql date time 14 28
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

785 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