pivot sql convert rows to column

Question

I have a table with results like the following,

    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

I would like the result to be displayed as

    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

try with the follow code does not work, with error message "Invalid column name '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

Thanks so much guys!

Answer 1

your pivot is based on your column U3l_ReferenceID, so you need to have a subquery that will show this column again on your final select.

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