透视 SQL 将行转换为列

Question

>我有一个表格，结果如下，

MailoutId   U3l_ReferenceID
A10AF00B-DAA4-40DF-A8CC-08D46560240D    D858B7DF-B834-47EF-9E7E-08D2AEC34D4C
A10AF00B-DAA4-40DF-A8CC-08D46560240D    83562373-0BD9-47FA-99DF-08D2AEC34FAE
28DF6E8E-EE8A-4FF4-9E2E-08D404079608    33DB9970-8927-405B-8E0D-08D2AEC365B2
3A4D0FBE-B8FF-4BB8-8A39-08D4040B1528    33DB9970-8927-405B-8E0D-08D2AEC365B2
64F51146-6690-44F5-BF61-08D411F80786    33DB9970-8927-405B-8E0D-08D2AEC365B2
8FE88373-B7DD-44C9-8BA1-08D4191C7EEF    33DB9970-8927-405B-8E0D-08D2AEC365B2
A10AF00B-DAA4-40DF-A8CC-08D46560240D    33DB9970-8927-405B-8E0D-08D2AEC365B2
613B07A7-C31A-444A-9420-08D407F45917    33DB9970-8927-405B-8E0D-08D2AEC365B2
65FA1F40-4CFE-4FDF-AA43-08D4A69B4811    33DB9970-8927-405B-8E0D-08D2AEC365B2
8FE88373-B7DD-44C9-8BA1-08D4191C7EEF    CDDDEA88-7E38-490B-B8AC-08D2AEC3795D
A10AF00B-DAA4-40DF-A8CC-08D46560240D    CDDDEA88-7E38-490B-B8AC-08D2AEC3795D
3A4D0FBE-B8FF-4BB8-8A39-08D4040B1528    CDDDEA88-7E38-490B-B8AC-08D2AEC3795D
28DF6E8E-EE8A-4FF4-9E2E-08D404079608    CDDDEA88-7E38-490B-B8AC-08D2AEC3795D
613B07A7-C31A-444A-9420-08D407F45917    CDDDEA88-7E38-490B-B8AC-08D2AEC3795D
64F51146-6690-44F5-BF61-08D411F80786    CDDDEA88-7E38-490B-B8AC-08D2AEC3795D
65FA1F40-4CFE-4FDF-AA43-08D4A69B4811    CDDDEA88-7E38-490B-B8AC-08D2AEC3795D

我希望结果显示为

U3l_ReferenceID                         A10AF00B-DAA4-40DF-A8CC-08D46560240D 28DF6E8E-EE8A-4FF4-9E2E-08D404079608 3A4D0FBE-B8FF-4BB8-8A39-08D4040B1528 64F51146-6690-44F5-BF61-08D411F80786
D858B7DF-B834-47EF-9E7E-08D2AEC34D4C    Yes                                  No                                   No                                   No
83562373-0BD9-47FA-99DF-08D2AEC34FAE    Yes                                  No                                   No                                   No
28DF6E8E-EE8A-4FF4-9E2E-08D404079608    No                                   Yes                                  Yes                                  Yes

尝试使用以下代码不起作用，并显示错误消息"列名称'U3l_ReferenceID无效'

with RefM as 
(
select distinct MailoutId, U3l_ReferenceID from u3_data.data.maillog_568c1b984fd0405ebf9508d26c224e79 ml with(nolock)
where campaignId = '85EB250E-A20F-4DA6-BDCB-08D3E6F40463'
and status = 'Delivered'
)
select a.U3l_ReferenceID,[28DF6E8E-EE8A-4FF4-9E2E-08D404079608],[3A4D0FBE-B8FF-4BB8-8A39-08D4040B1528],[613B07A7-C31A-444A-9420-08D407F45917] from
RefM 
pivot (
count(U3l_ReferenceID)
For mailoutID in (
[28DF6E8E-EE8A-4FF4-9E2E-08D404079608],
[3A4D0FBE-B8FF-4BB8-8A39-08D4040B1528],
[613B07A7-C31A-444A-9420-08D407F45917],
[64F51146-6690-44F5-BF61-08D411F80786],
[8FE88373-B7DD-44C9-8BA1-08D4191C7EEF],
[A10AF00B-DAA4-40DF-A8CC-08D46560240D],
[65FA1F40-4CFE-4FDF-AA43-08D4A69B4811],
[B13DC287-1038-4C5C-ADFC-08D40402F0B4],
[C121CDE4-64D4-4A0D-B9E4-08D525CF15A4],
[4DB18698-4693-457F-9617-08D72D633976])
) as pivotTable

非常感谢大家！

Answer 1

您的pivot基于您的列U3l_ReferenceID，因此您需要有一个子查询，该子查询将在最终选择时再次显示此列。

with RefM as 
(
select distinct MailoutId, U3l_ReferenceID from test3 ml with(nolock)
where campaignId = '85EB250E-A20F-4DA6-BDCB-08D3E6F40463'
and status = 'Delivered'
)
select * from (
select U3l_ReferenceID, U3l_ReferenceID as [U3l ReferenceID],
mailoutID       
from RefM
) t
pivot (
count(U3l_ReferenceID)
For mailoutID in (      
[28DF6E8E-EE8A-4FF4-9E2E-08D404079608],
[3A4D0FBE-B8FF-4BB8-8A39-08D4040B1528],
[613B07A7-C31A-444A-9420-08D407F45917],
[64F51146-6690-44F5-BF61-08D411F80786],
[8FE88373-B7DD-44C9-8BA1-08D4191C7EEF],
[A10AF00B-DAA4-40DF-A8CC-08D46560240D],
[65FA1F40-4CFE-4FDF-AA43-08D4A69B4811],
[B13DC287-1038-4C5C-ADFC-08D40402F0B4],
[C121CDE4-64D4-4A0D-B9E4-08D525CF15A4],
[4DB18698-4693-457F-9617-08D72D633976]) 
) as t1