Want to populate a Data Extension using Query from another Data Extension

Question

I have 2 Data Extenstion DE1 and DE2. I want to insert data into DE2 based on conditions of DE1.

Say DE1 has data

SID LinkName           
S1  Link1           
S1  Link2           
S2  Link1           
S2  Link2          
S2  Link3         
S3  Link2

I want to insert data into a new table DE2 having above Link1, Link2 etc as columns in such a way that if S1 has link 1 then its column for Link1 would show Yes and so on. Example for S3, column of Link1 and Link3 will show NO, Link2 Column will show YES

SID Link1 Link2 Link3                   
S1  Yes   Yes   No                   
S2  Yes   Yes   Yes                  
S3  No    Yes   No

Is it possible using query activity?

Answer 1

Sure. It'd be something like this, since SFMC doesn't support T-SQL pivot.

CREATE TABLE Table1
    ([SID] varchar(2), [LinkName] varchar(5))
;

INSERT INTO Table1
    ([SID], [LinkName])
VALUES
    ('S1', 'Link1'),
    ('S1', 'Link2'),
    ('S2', 'Link1'),
    ('S2', 'Link2'),
    ('S2', 'Link3'),
    ('S3', 'Link2')
;
select 
SID
, max(case when LinkName = 'Link1' then 'Yes' else 'No' end) as Link1
, max(case when LinkName = 'Link2' then 'Yes' else 'No' end) as Link2
, max(case when LinkName = 'Link3' then 'Yes' else 'No' end) as Link3
from table1
group by SID

Output

| Row | SID | Link1 | Link2 | Link3 |
|-----|-----|-------|-------|-------|
| 1   | S1  | Yes   | Yes   | No    |
| 2   | S2  | Yes   | Yes   | Yes   |
| 3   | S3  | No    | Yes   | No    |

Test: http://rextester.com/SPEYJ40747