Return all values belonging to the ID in one SQL row

Question

I have a table that has several records for each ID.

I want him to bring me in one row in a separate column all the records that belong to the ID.

For example:

object_id:	post_title:	meta_key :	meta_value
302	CHICKEN CHOW MEIN	post_image	url1
302	CHICKEN CHOW MEIN	price	6.95

I want it to look like this:

object_id:	post_title:	meta_key
302	CHICKEN CHOW MEIN	post_image, price

Query:

SELECT 
    wp_term_relationships.object_id, wp_posts.post_title,                                        
    wp_postmeta.meta_key, wp_postmeta.meta_value
FROM 
    `wp_posts`, wp_postmeta, wp_term_relationships
WHERE 
    (wp_posts.ID = wp_postmeta.post_id)
    AND (wp_postmeta.post_id = wp_term_relationships.object_id)  
    AND wp_term_relationships.term_taxonomy_id = 33

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**30 years!!** ago) and its use is discouraged — marc_s, May 22 '22 at 11:03
Although judging by the fact that you are using backticks `\`\`` to quote tables, are you using MySQL? In which case you need `GROUP_CONCAT()` — Charlieface, May 22 '22 at 11:45
Considering the object names, which appear to be WordPress, that further suggests MySQL rather than SQL Server; though there are tools to install WordPress on a SQL Server backend. — Thom A, May 22 '22 at 14:46

RF1991 · Answer 1 · 2022-05-22T12:09:37.450

your data

declare @a table(
   object_id  INTEGER  NOT NULL 
  ,post_title VARCHAR(18) NOT NULL
  ,meta_key  VARCHAR(11) NOT NULL
  ,meta_value VARCHAR(4) NOT NULL
);
INSERT INTO @a
(object_id,post_title,meta_key,meta_value) VALUES 
(302,'CHICKEN CHOW MEIN','post_image','url1'),
(302,'CHICKEN CHOW MEIN','price','6.95');

use String_agg for SQL Server 2017 and newer versions

SELECT object_id,
       post_title,
       String_agg(meta_key, ',') meta_key
FROM   @a
GROUP  BY object_id,
          post_title

for older versions use FOR XML PATH

SELECT object_id,
       post_title,
       Stuff((SELECT ',' + a1.meta_key
              FROM   @a a1
              WHERE  a1.object_id = a2.object_id
              ORDER  BY a1.meta_key DESC
              FOR xml
       path(''), type).value('(./text())[1]', 'varchar(MAX)'), 1, 1, '') meta_key
FROM   @a a2
GROUP  BY a2.object_id,
          a2.post_title;

In your queries,use Subquery and standard and new form of join form as follows

SELECT object_id,
       post_title,
       String_agg(meta_key, ',') meta_key
FROM   (SELECT wp_term_relationships.object_id,
               wp_posts.post_title,
               wp_postmeta.meta_key,
               wp_postmeta.meta_value
        FROM   wp_postmeta
               JOIN wp_posts
                 ON wp_posts.id = wp_postmeta.post_id
               JOIN wp_term_relationships
                 ON wp_postmeta.post_id = wp_term_relationships.object_id
                    AND wp_term_relationships.term_taxonomy_id = 33) A
GROUP  BY object_id,
          post_title

or use CTE and standard and new form of join form

with a as
(
SELECT wp_term_relationships.object_id,
            wp_posts.post_title,
            wp_postmeta.meta_key,
            wp_postmeta.meta_value
    FROM   wp_postmeta
            JOIN wp_posts
                ON wp_posts.id = wp_postmeta.post_id
            JOIN wp_term_relationships
                ON wp_postmeta.post_id = wp_term_relationships.object_id
                AND wp_term_relationships.term_taxonomy_id = 33
)

SELECT object_id,
       post_title,
       Stuff((SELECT ',' + a1.meta_key
              FROM   a a1
              WHERE  a1.object_id = a2.object_id
              ORDER  BY a1.meta_key DESC
              FOR xml
       path(''), type).value('(./text())[1]', 'varchar(MAX)'), 1, 1, '') meta_key
FROM   a a2
GROUP  BY a2.object_id,
          a2.post_title;

Return all values ​belonging to the ID in one SQL row

1 Answers1

Return all values belonging to the ID in one SQL row