SQL - reconfigure table via query
I have a poorly designed table that I inherited. It looks like this:
User Field Value
-------------------
1 name Aaron
1 email aaron@company.com
1 phone 800-555-4545
2 name Mike
2 email mike@group.org
2 phone 777-123-4567
(etc, etc)
I would like to retrieve this data using a query in a more reasonable format:
User Name Email Phone ------------------------------------------- 1 Aaron aaron@company.com 800-555-4545 2 Mike mike@group.org 777-123-4567
I am a SQL newbie but tried several queries with Group By variations, all without anything even close to success.
Is there a SQL way to simplify?
a source to share
In my job, we wish we had such a database design. But this design works better for us than traditional database design because of the different records we have to store and gives us the flexibility we need. The database we use holds millions of records.
This would be the fastest way to run a query on a large database using MSSQL. This eliminates the need to do so many joins, which can be very costly.
DECLARE @Results TABLE
(
UserID INT
, Name VARCHAR(50)
, Email VARCHAR(50)
, Phone VARCHAR(50)
)
INSERT INTO @Results
SELECT DISTINCT User FROM UserValues
UPDATE
R
SET
R.Name = UV.Value
FROM
@Results R
INNER JOIN
UserValues UV
ON UV.User = R.UserID
WHERE
UV.Field = 'name'
UPDATE
R
SET
R.Email = UV.Value
FROM
@Results R
INNER JOIN
UserValues UV
ON UV.User = R.UserID
WHERE
UV.Field = 'Email'
UPDATE
R
SET
R.Phone = UV.Value
FROM
@Results R
INNER JOIN
UserValues UV
ON UV.User = R.UserID
WHERE
UV.Field = 'Phone'
SELECT * FROM @Results
a source to share
it is not a "badly designed table"; but it is actually an attribute attribute table (EAV). Unfortunately, relational databases are poor platforms for implementing such tables and negate most of the nice things about RDBMSs. A common case of using the wrong shovel to nail the screw.
but I think it will work (based on Marcus Adams answer which I think will not work (edit: it does it now))
SELECT User1.Value AS name, User2.Value AS email, User3.Value AS phone
FROM Users User1
LEFT JOIN Users User2
ON User2.User = User1.User AND User2.Field='email'
LEFT JOIN Users User3
ON User3.User = User1.User AND User3.Field='phone'
WHERE User1.Field = 'name'
ORDER BY User1.User
Edit: Got some subtleties from other answers (LEFT Joins and field names in ON clauses), now does anyone know how to add the remaining WHEREs a little higher? (but not on the first JOIN ON, it's too ugly) of course it doesn't matter as the query optimizer guesses it anyway.
a source to share
You can use a self-connection:
SELECT User1.User, User1.Value as Name, User2.Value as Email,
User3.Value as Phone
FROM Users User1
JOIN Users User2
ON User2.User = User1.User
JOIN Users User3
ON User3.User = User1.User
WHERE User1.Field = 'name' AND User2.Field = 'email' AND User3.Field = 'phone'
ORDER BY User1.User
I tested this query and it works.
a source to share
I believe this will create the result set you are looking for. From there, you can create a view or use the data to populate a new table.
select user, name, email, phone from
(select user, value as name from table where field='name')
natural join
(select user, value as email from table where field='email')
natural join
(select user, value as phone from table where field='phone')
a source to share
In MySQL, you can do something like this:
SELECT
id,
group_concat(CASE WHEN field='name' THEN value ELSE NULL END) AS name,
group_concat(CASE WHEN field='phone' THEN value ELSE NULL END) AS phone,
...
FROM test
GROUP BY id
The general function doesn't actually matter if you only have one field of each type. You can also use min()
or max()
with the same effect.
a source to share
Answer option Javier who has my vote.
SELECT
UserName.name, UserEmail.email, UserPhone.phone
FROM
Users AS UserName
INNER JOIN Users AS UserEmail ON UserName.User = UserEmail.User
AND UserName.field = 'name' AND UserEmail.field = 'email'
INNER JOIN Users AS UserPhone ON UserName.User = UserPhone.User
AND UserPhone.field = 'phone'
Use LEFT JOINs
if not all attributes are guaranteed to exist. A component index over would (User,Field)
probably be helpful for this.
a source to share