Merge mysql columns to rows
1
I have a table with the following structure:
+-----+------------+------------+------------+------------+
| ID | FEATURE1 | FEATURE2 | FEATURE3 | FEATURE4 |
+-----+------------+------------+------------+------------+
| 1 | red | blue | black | grey |
| 2 | yellow | | | |
| 3 | orange | red | green | golden |
| 4 | orange | grey | blue | |
What I would like to do is query the table and return all possible features for use in an autocomplete call. I need the query to only return unique values.
SELECT (FEATURE1, FEATURE2, FEATURE3, FEATURE4, FEATURE5) FEATURE
FROM products
WHERE `FEATURE` LIKE 'gr%'
My desired output would be:
+----------+
| FEATURE |
+----------+
| green |
| grey |
mysql sql merge
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
asked Mar 15 '13 at 14:47
user2122889user2122889
62
add a comment |
1
I have a table with the following structure:
+-----+------------+------------+------------+------------+
| ID | FEATURE1 | FEATURE2 | FEATURE3 | FEATURE4 |
+-----+------------+------------+------------+------------+
| 1 | red | blue | black | grey |
| 2 | yellow | | | |
| 3 | orange | red | green | golden |
| 4 | orange | grey | blue | |
What I would like to do is query the table and return all possible features for use in an autocomplete call. I need the query to only return unique values.
SELECT (FEATURE1, FEATURE2, FEATURE3, FEATURE4, FEATURE5) FEATURE
FROM products
WHERE `FEATURE` LIKE 'gr%'
My desired output would be:
+----------+
| FEATURE |
+----------+
| green |
| grey |
mysql sql merge
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
asked Mar 15 '13 at 14:47
user2122889user2122889
62
concat all columns and filter with 'gr%'.
– Vinit Prajapati
Mar 15 '13 at 14:49
Return unique values or distinct values ?
– A Hocevar
Mar 15 '13 at 14:57
add a comment |
1
1
1
I have a table with the following structure:
+-----+------------+------------+------------+------------+
| ID | FEATURE1 | FEATURE2 | FEATURE3 | FEATURE4 |
+-----+------------+------------+------------+------------+
| 1 | red | blue | black | grey |
| 2 | yellow | | | |
| 3 | orange | red | green | golden |
| 4 | orange | grey | blue | |
What I would like to do is query the table and return all possible features for use in an autocomplete call. I need the query to only return unique values.
SELECT (FEATURE1, FEATURE2, FEATURE3, FEATURE4, FEATURE5) FEATURE
FROM products
WHERE `FEATURE` LIKE 'gr%'
My desired output would be:
+----------+
| FEATURE |
+----------+
| green |
| grey |
mysql sql merge
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
asked Mar 15 '13 at 14:47
user2122889user2122889
62
I have a table with the following structure:
+-----+------------+------------+------------+------------+
| ID | FEATURE1 | FEATURE2 | FEATURE3 | FEATURE4 |
+-----+------------+------------+------------+------------+
| 1 | red | blue | black | grey |
| 2 | yellow | | | |
| 3 | orange | red | green | golden |
| 4 | orange | grey | blue | |
What I would like to do is query the table and return all possible features for use in an autocomplete call. I need the query to only return unique values.
SELECT (FEATURE1, FEATURE2, FEATURE3, FEATURE4, FEATURE5) FEATURE
FROM products
WHERE `FEATURE` LIKE 'gr%'
My desired output would be:
+----------+
| FEATURE |
+----------+
| green |
| grey |
mysql sql merge
mysql sql merge
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
asked Mar 15 '13 at 14:47
user2122889user2122889
62
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
asked Mar 15 '13 at 14:47
user2122889user2122889
62
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
edited Nov 13 '18 at 5:11
Cœur
17.7k9106145
17.7k9106145
asked Mar 15 '13 at 14:47
user2122889user2122889
62
asked Mar 15 '13 at 14:47
user2122889user2122889
62
asked Mar 15 '13 at 14:47
user2122889user2122889
62
62
concat all columns and filter with 'gr%'.
– Vinit Prajapati
Mar 15 '13 at 14:49
Return unique values or distinct values ?
– A Hocevar
Mar 15 '13 at 14:57
add a comment |
concat all columns and filter with 'gr%'.
– Vinit Prajapati
Mar 15 '13 at 14:49
Return unique values or distinct values ?
– A Hocevar
Mar 15 '13 at 14:57
concat all columns and filter with 'gr%'.
– Vinit Prajapati
Mar 15 '13 at 14:49
concat all columns and filter with 'gr%'.
– Vinit Prajapati
Mar 15 '13 at 14:49
Return unique values or distinct values ?
– A Hocevar
Mar 15 '13 at 14:57
Return unique values or distinct values ?
– A Hocevar
Mar 15 '13 at 14:57
add a comment |
1 Answer
1
active
oldest
votes
4
Basically, you can use UNION to combine all columns in a single row.
SELECT *
FROM
(
SELECT FEATURE1 FEATURE FROM TableName
UNION
SELECT FEATURE2 FEATURE FROM TableName
UNION
SELECT FEATURE3 FEATURE FROM TableName
UNION
SELECT FEATURE4 FEATURE FROM TableName
) sub
WHERE FEATURE LIKE 'gr%'
- SQLFiddle Demo
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
Thanks, that works perfect. I have a few questions. Should I be adding indexes on the FEATURE columns?
– user2122889
Mar 15 '13 at 16:33
you're welcome:Dwhat is it?
– John Woo
Mar 15 '13 at 16:34
Should I be adding indexes on the FEATURE columns? How does MYSQL know to select DISTINCT values? Should I really be creating a separate table to store the features if I wish this table to hold a large number of records?
– user2122889
Mar 15 '13 at 16:35
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f15435346%2fmerge-mysql-columns-to-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
Basically, you can use UNION to combine all columns in a single row.
SELECT *
FROM
(
SELECT FEATURE1 FEATURE FROM TableName
UNION
SELECT FEATURE2 FEATURE FROM TableName
UNION
SELECT FEATURE3 FEATURE FROM TableName
UNION
SELECT FEATURE4 FEATURE FROM TableName
) sub
WHERE FEATURE LIKE 'gr%'
- SQLFiddle Demo
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
Thanks, that works perfect. I have a few questions. Should I be adding indexes on the FEATURE columns?
– user2122889
Mar 15 '13 at 16:33
you're welcome:Dwhat is it?
– John Woo
Mar 15 '13 at 16:34
Should I be adding indexes on the FEATURE columns? How does MYSQL know to select DISTINCT values? Should I really be creating a separate table to store the features if I wish this table to hold a large number of records?
– user2122889
Mar 15 '13 at 16:35
add a comment |
4
Basically, you can use UNION to combine all columns in a single row.
SELECT *
FROM
(
SELECT FEATURE1 FEATURE FROM TableName
UNION
SELECT FEATURE2 FEATURE FROM TableName
UNION
SELECT FEATURE3 FEATURE FROM TableName
UNION
SELECT FEATURE4 FEATURE FROM TableName
) sub
WHERE FEATURE LIKE 'gr%'
- SQLFiddle Demo
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
Thanks, that works perfect. I have a few questions. Should I be adding indexes on the FEATURE columns?
– user2122889
Mar 15 '13 at 16:33
you're welcome:Dwhat is it?
– John Woo
Mar 15 '13 at 16:34
Should I be adding indexes on the FEATURE columns? How does MYSQL know to select DISTINCT values? Should I really be creating a separate table to store the features if I wish this table to hold a large number of records?
– user2122889
Mar 15 '13 at 16:35
add a comment |
4
4
4
Basically, you can use UNION to combine all columns in a single row.
SELECT *
FROM
(
SELECT FEATURE1 FEATURE FROM TableName
UNION
SELECT FEATURE2 FEATURE FROM TableName
UNION
SELECT FEATURE3 FEATURE FROM TableName
UNION
SELECT FEATURE4 FEATURE FROM TableName
) sub
WHERE FEATURE LIKE 'gr%'
- SQLFiddle Demo
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
Basically, you can use UNION to combine all columns in a single row.
SELECT *
FROM
(
SELECT FEATURE1 FEATURE FROM TableName
UNION
SELECT FEATURE2 FEATURE FROM TableName
UNION
SELECT FEATURE3 FEATURE FROM TableName
UNION
SELECT FEATURE4 FEATURE FROM TableName
) sub
WHERE FEATURE LIKE 'gr%'
- SQLFiddle Demo
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
answered Mar 15 '13 at 14:48
John WooJohn Woo
205k46388421
205k46388421
Thanks, that works perfect. I have a few questions. Should I be adding indexes on the FEATURE columns?
– user2122889
Mar 15 '13 at 16:33
you're welcome:Dwhat is it?
– John Woo
Mar 15 '13 at 16:34
Should I be adding indexes on the FEATURE columns? How does MYSQL know to select DISTINCT values? Should I really be creating a separate table to store the features if I wish this table to hold a large number of records?
– user2122889
Mar 15 '13 at 16:35
add a comment |
Thanks, that works perfect. I have a few questions. Should I be adding indexes on the FEATURE columns?
– user2122889
Mar 15 '13 at 16:33
you're welcome:Dwhat is it?
– John Woo
Mar 15 '13 at 16:34
Should I be adding indexes on the FEATURE columns? How does MYSQL know to select DISTINCT values? Should I really be creating a separate table to store the features if I wish this table to hold a large number of records?
– user2122889
Mar 15 '13 at 16:35
Thanks, that works perfect. I have a few questions. Should I be adding indexes on the FEATURE columns?
– user2122889
Mar 15 '13 at 16:33
Thanks, that works perfect. I have a few questions. Should I be adding indexes on the FEATURE columns?
– user2122889
Mar 15 '13 at 16:33
you're welcome
:D what is it?– John Woo
Mar 15 '13 at 16:34
you're welcome
:D what is it?– John Woo
Mar 15 '13 at 16:34
Should I be adding indexes on the FEATURE columns? How does MYSQL know to select DISTINCT values? Should I really be creating a separate table to store the features if I wish this table to hold a large number of records?
– user2122889
Mar 15 '13 at 16:35
Should I be adding indexes on the FEATURE columns? How does MYSQL know to select DISTINCT values? Should I really be creating a separate table to store the features if I wish this table to hold a large number of records?
– user2122889
Mar 15 '13 at 16:35
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f15435346%2fmerge-mysql-columns-to-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
concat all columns and filter with 'gr%'.
– Vinit Prajapati
Mar 15 '13 at 14:49
Return unique values or distinct values ?
– A Hocevar
Mar 15 '13 at 14:57