将多行中的值提取/选择到 sql server 中的 1 个单行中 [英] Fetch/select values from multiple rows into 1 single row in sql server

问题描述

我有一张表格，每列都有一些图片.

I have a table with some images in each column.

Acd_unq_id  Emp_unq_id  Acd_BImg1   Acd_BImg2  Acd_RImg1  Acd_RImp2  Acd_RImg3  Acd_Active

1          1745     BinaryImg   BinaryImg     NULL      NULL       NULL         1
2          1745        NULL        NULL    BinaryImg    NULL       NULL         1
3          1745        NULL        NULL       NULL      BinaryImg  BinaryImg    1
4          1769     BinaryImg   BinaryImg     NULL      NULL       BinaryImg    1

在上面的示例中，我想为每个唯一的 Emp ID 获取单个列中的数据.所以根据上面给出的例子，我希望结果如下 -

In the above example, I want to get the data in one single column for every unique Emp ID. So according to the above given example, I want the result to be as follows -

Acd_unq_id  Emp_unq_id  Acd_BImg1   Acd_BImg2  Acd_RImg1  Acd_RImp2  Acd_RImg3  Acd_Active

1          1745     BinaryImg   BinaryImg  BinaryImg  BinaryImg  BinaryImg     1
4          1769     BinaryImg   BinaryImg     NULL      NULL     BinaryImg     1

我为此写了一个查询 -

I have written a query for this -

SELECT
(
SELECT TOP 1 ACH_BILL_IMG1 FROM ADVERT_CAMPAIGN_HEADERS INNER JOIN ADVERT_CAMPAIGN_HEADERS
ON ACH_UNQ_ID = ACD_ACH_UNQ_ID WHERE ACD_SOC_UNQ_ID = 1745 AND ACD_IMAGETYPE IN ('H, Q')) as bill_img1

(SELECT TOP 1 ACH_BILL_IMG2 FROM ADVERT_CAMPAIGN_HEADERS INNER JOIN ADVERT_CAMPAIGN_HEADERS
ON ACH_UNQ_ID = ACD_ACH_UNQ_ID WHERE ACD_SOC_UNQ_ID = 1745 AND ACD_IMAGETYPE IN ('H, Q'))

我想使用子查询和前 1 获取结果.但是在我编写的上述示例查询中，我遇到了语法错误....

I want to get the result using subquery and top 1. But in the above sample query I have written I am getting syntax error....

*FROM 子句中的对象ADVERT_CAMPAIGN_HEADERS"和ADVERT_CAMPAIGN_HEADERS"具有相同的公开名称.使用相关名称来区分它们.*

*The objects "ADVERT_CAMPAIGN_HEADERS" and "ADVERT_CAMPAIGN_HEADERS" in the FROM clause have the same exposed names. Use correlation names to distinguish them.*

请帮忙.等待您的答复，专家:)

Please help. Waiting for your reply, experts :)

推荐答案

怎么样:

SELECT [Acd_unq_id] = MIN([Acd_unq_id])
, [Emp_unq_id]
, [Acd_BImg1] = MAX([Acd_BImg1])
, [Acd_BImg2] = MAX([Acd_BImg2])
, [Acd_RImg1] = MAX([Acd_RImg1])
, [Acd_RImp2] = MAX([Acd_RImp2])
, [Acd_RImg3] = MAX([Acd_RImg3])
, [Acd_Active] = MAX([Acd_Active])
FROM ADVERT_CAMPAIGN_HEADERS
GROUP BY [Emp_unq_id]

这篇关于将多行中的值提取/选择到 sql server 中的 1 个单行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！