CODEWITHSUNDEEP
sqlsql-server
Arpit Tripathi
Arpit Tripathi

Reputation: 55

How can I aggregate the columns with other data?

As a part of output from my joins in SP is like :

Col1 col2
A     1
B     1
C     2
C     1

I have another table that has all the possible values in Col1 (A,B,C,D,E,F) [The values are not known and as best practice I don't want to hardcode these values. ]

Expected output in SQL is as shown below

Col1   1            2
A     Yes        No/Null
B     Yes        No
C     Yes        Yes
D      No         No 
E      No         No
F      No         No

Appreciate the help I have relatively less experience in SQL and would appreciate if someone could help me understand how I can achieve this.

Upvotes: 0

Views: 66

Answers (2)

Eralper
Eralper

Reputation: 6612

Following SQL pivot query can be used if you can use '1/0' instead of 'YES/NO'

select
 *
from (
    select 
        letters.col1, mydata.col2
    from (
    select 'A' as col1 union all select 'B' union all select 'C' union all select 'D' union all select 'E' union all select 'F'
    ) letters
    left join (
        select 'A' as col1, 1 as col2 union all
        select 'B' as col1, 1 as col2 union all
        select 'C' as col1, 2 as col2 union all
        select 'C' as col1, 1 as col2
    ) mydata
        on mydata.col1 = letters.col1
) data
PIVOT (
 count( col2 )
 FOR col2
 IN (
  [1],[2]
 )
) PivotTable

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do JOIN with conditional aggregation : 

with cte as (
     < query >
)
select c.col1, 
       max(case when t1.col2 = 1 then 'Yes' end),
       max(case when t1.col2 = 2 then 'Yes' end)
from cte c LEFT JOIN
     table t1
     on t1.col1 = c.col1
group by c.col1;

Upvotes: 3

Related Questions