mySQL query returns 3 columns, I want to get values from one column concatenated into comma separated string
mysql get comma separated values from multiple rows
mysql comma separated values to rows
mysql query finding values in a comma separated string
query for comma-separated ids to comma-separated values
how to get multiple column values in comma separated sql server
how to get column values in comma separated sql server
I need to get the values from the meta_value column that have "shipping" in the corresponding meta_key column and concatenate them into a comma separated row.
I'm a bit of a noob to SQL so please be gentle.
My current query is this:
SELECT wp_postmeta.post_ID, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID, wp_posts.post_status FROM wp_postmeta CROSS JOIN wp_posts WHERE wp_postmeta.post_ID= wp_posts.ID AND wp_posts.post_status LIKE 'wc-processing' AND (wp_postmeta.meta_key LIKE '%shipping%' OR wp_postmeta.meta_key LIKE '_order_total')
So, here's your query (note how I write it - I find that good formatting of SQL helps to read it):
SELECT wp_postmeta.post_ID, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID, wp_posts.post_status FROM wp_postmeta CROSS JOIN wp_posts WHERE wp_postmeta.post_ID = wp_posts.ID AND wp_posts.post_status LIKE 'wc-processing' AND (wp_postmeta.meta_key LIKE '%shipping%' OR wp_postmeta.meta_key LIKE '_order_total')
Here's what I think you want (you use the MySQL concat function):
Select concat( wp_postmeta.meta_key, ',', wp_postmeta.meta_value ) as formatted_row FROM wp_postmeta INNER JOIN wp_posts ON wp_postmeta.post_ID = wp_posts.ID WHERE wp_posts.post_status LIKE 'wc-processing' AND ( wp_postmeta.meta_key LIKE '%shipping%' OR wp_postmeta.meta_key LIKE '_order_total' )
Also note: Single quotes and double quotes mean different things in different databases.
How to Return Query Results as a Comma Separated List in MySQL , In MySQL, you can return your query results as a comma separated list by the purpose of concatenating a query's result set into a list separated by either a by the string literal value that should be inserted between group values. where you want to provide a list of results, grouped by another column. I want to convert selected values into a comma separated string in MySQL. My initial code is as follows: SELECT id FROM table_level where parent_id=4;
You can use
GROUP_CONCAT to join the strings together. As has been pointed out by @kermit, you should use an
INNER JOIN not a
CROSS JOIN. I am presuming you want to keep the order total separate from the shipping details, so you will need to use conditional aggregation as well. This query should do what you want:
SELECT m.post_ID , GROUP_CONCAT(CASE WHEN m.meta_key LIKE '%shipping%' THEN m.meta_value END) AS shipping_info , MAX(CASE WHEN m.meta_key = '_order_total' THEN m.meta_value ELSE 0 END) AS order_total FROM wp_postmeta m INNER JOIN wp_posts p ON m.post_ID = p.ID WHERE p.post_status LIKE 'wc-processing' AND (m.meta_key LIKE '%shipping%' OR m.meta_key LIKE '_order_total')
Converting row values in a table to a single concatenated string , The query needs to return one row and one column so that the results pane in SQL SELECT +','++','++','++','++','++','++','+ AS Txt Normally we would have some type of aggregation as the rows are pivoted to columns, of this, but strips out the XML specific parts to produce the comma separated list. Forgive me for not directly answering the question, but why would you waste precious time hoping for help from Quora when you could just read the relevant part of the MySQL documentation (13.2.8 SELECT Syntax) yourself?
this should do the trick its a bit more involved than on sql server but easy enough
create table if not exists test(col1 varchar(10),col2 varchar(10), col3 varchar(10)); insert into test values("this","is","test"); select concat(col1,",",col2,",",col3) as csvCol from test
How to show a column result in a one line comma separated list in , Often I need to extract a list of articles IDs from my MySql databases but getting the To concatenate multiple rows results into a single mySql field you can use the Let's see a real life example query where we can apply this function. by the group_concat_max_len system variable, which has a default value of 1024. But I need the result to be stored in a table as I want to generate a chart from it. Is it possible to store the pivoted result into a table? Also, is it possible to execute above in Php? My source table is user, date, performance and pivot is user as rows, date as columns and performance as cell values. – Sri Jun 15 '13 at 20:00
MySQL GROUP_CONCAT Function By Practical Examples, The MySQL GROUP_CONCAT() function is an aggregate function that concatenates strings from a group into a single string with To get all countries where customers locate as a comma-separated string, you use For example, the GROUP_CONCAT() function returns the result of values: 1 2 , and 3 as the '1,2,3' string. This a stab at creating a reusable column to comma separated string. In this case, I only one strings that have values and I do not want empty strings or nulls. First I create a user defined type that is a one column table.
SQL Hacks: Tips & Tools for Digging Into Your Data, Tips & Tools for Digging Into Your Data Andrew Cumming, Gordon Russell. #84 Store Comma-Delimited Lists in a Column , categories.name FROM articles In MySQL you can use CONCAT('%', categories.id, '%'), and in SQL Server you data, this query now searches the string ',3,9,37,53,82,' for a LIKE expression of '% MySQL CONCAT() function is used to add two or more strings. There may be one or more arguments. Returns the string that results from concatenating the arguments. Returns a nonbinary string, if all arguments are nonbinary strings. Returns a binary string, if the arguments include any binary strings.
How to split a comma separated string into multiple column in mysql, We want to join the values in the CustomerID column into a comma delimited one function that will return table based on comma seprated string & delimiter value. I want to break that data into three columns (street, city and state) respectively select query as a string inside a variable and then concatenate in the comma 184.108.40.206 Selecting Particular Columns If you do not want to see entire rows from your table, just name the columns in which you are interested, separated by commas. For example, if you want to know when your animals were born, select the name and birth columns:
- can you please provide some snippets of your code?
- You don't want a cross join (which is also called a cartesian product). That gives you the number of records in the first table X the number of records in the 2nd table. You probably want an inner join, which gives you matching records. FYI, the remaining joins are left, right and full. There's a nice Venn diagram out there: blog.codinghorror.com/a-visual-explanation-of-sql-joins
- What will be expected output, as per your given sample data ?
- Did one of these answers solve your problem? If not, could you provide more information to help answer it? Otherwise, please consider marking the answer which best solved your problem accepted. See stackoverflow.com/help/someone-answers
- Apologies all for my lack of response. I got snowed under at work and haven't had time to test these out. I will endeavour to do so in the next couple of days and will respond then. Thanks for all your help.