取多行/选择值到SQL服务器1个单列(Fetch/select values from multip

我有在每列一些图像的表。

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一个单一的列中的数据。 因此，根据上面给出的例子，我希望得到的结果是如下 -

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

我写这个查询 -

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的结果，但在上面的示例查询我写我得到语法错误....

* FROM子句中具有相同的暴露名的对象“ADVERT_CAMPAIGN_HEADERS”和“ADVERT_CAMPAIGN_HEADERS”。 使用相关名称来区分它们。*

请帮忙。 等待你的答复，专家:)

Answer 1:

怎么样：

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]

Answer 2:

试试这个

`Select xx.Emp_unq_id,Acd_BImg1,Acd_BImg2,Acd_RImg1,Acd_RImg2,Acd_RImg3 from
((select  Emp_unq_id,Acd_BImg1 from yourtab where Emp_unq_id =1745
and Acd_BImg1 is not null)A 
inner join
select  Emp_unq_id,Acd_BImg2 from yourtab where Emp_unq_id =1745
and Acd_BImg2 is not null) B 
on A.Emp_unq_id=B.Emp_unq_id
inner join
(select  Emp_unq_id,Acd_RImg1 from yourtab where Emp_unq_id =1745
and Acd_RImg1 is not null) C
on B.Emp_unq_id=C.Emp_unq_id
inner join
(select  Emp_unq_id,Acd_RImg2 from yourtab where Emp_unq_id =1745
and Acd_RImg2 is not null) D
on C.Emp_unq_id=D.Emp_unq_id
inner join (select  Emp_unq_id,Acd_RImg3 from yourtab where Emp_unq_id =1745
and Acd_RImg3 is not null) E
on D.Emp_unq_id=E.Emp_unq_id)XX`

Repet这个查询其他员工ID也...

Answer 3:

从您的查询，我想只有一个图像列不为空值。 请注意，它可能给想要的结果，但我不从性能的角度来看知道。 我也期待着其他替代查询这一点。

;WITH tempCompaignHeaders(ACD_UNQ_ID,Acd_Active) AS
(
    SELECT DISTINCT Emp_unq_id, Acd_Active FROM ADVERT_CAMPAIGN_HEADERS
)
SELECT
       (SELECT TOP 1 ACD_UNQ_ID FROM ADVERT_CAMPAIGN_HEADERS WHERE Emp_unq_id = t.Emp_unq_id ORDER BY ACD_UNQ_ID) Acd_BImg1,
       Emp_unq_id
       (SELECT TOP 1 Acd_BImg1 FROM ADVERT_CAMPAIGN_HEADERS WHERE Emp_unq_id = t.Emp_unq_id AND Acd_BImg1 IS NOT NULL) Acd_BImg1,
       (SELECT TOP 1 Acd_BImg2 FROM ADVERT_CAMPAIGN_HEADERS WHERE Emp_unq_id = t.Emp_unq_id AND Acd_BImg2 IS NOT NULL) Acd_BImg2,
       (SELECT TOP 1 Acd_RImg1 FROM ADVERT_CAMPAIGN_HEADERS WHERE Emp_unq_id = t.Emp_unq_id AND Acd_RImg1 IS NOT NULL) Acd_RImg1,
       (SELECT TOP 1 Acd_RImg2 FROM ADVERT_CAMPAIGN_HEADERS WHERE Emp_unq_id = t.Emp_unq_id AND Acd_RImg2 IS NOT NULL) Acd_RImg2,
       (SELECT TOP 1 Acd_RImg3 FROM ADVERT_CAMPAIGN_HEADERS WHERE Emp_unq_id = t.Emp_unq_id AND Acd_RImg3 IS NOT NULL) Acd_RImg3,
       Acd_Active
FROM
       tempCompaignHeaders t

文章来源: Fetch/select values from multiple rows into 1 single row in sql server