返回一个SQL行中属于该ID的所有值

Question

我有一个表，每个ID有几条记录。

我想让他在一个单独的列的一行中为我带来属于ID的所有记录。

例如:

<表类>object_id:post_title:meta_key:meta_valuetbody><<tr>302鸡肉炒面post_imageurl1302鸡肉炒面价格6.95

您的数据

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');

在SQL Server 2017及更新版本中使用String_agg

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

旧版本使用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;  

在您的查询中，使用Subquery和standard和new form of join形式如下

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  

或使用CTE和standard和new form of join形式

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;