0

Hi I have the following scenario: DE 1:

JobID  |  CustomerID  |    Link       |
---------------------------------------
 123   |    122       | http://link1  |

 123   |    122       | http://link2  |

 123   |    122       | http://link3  |

 332   |    56        | http://link1  |

 332   |    56        | http://link2  |

I would like to get the data from the Data Extension above DE 1 and populate a new Data Extension with the following fields:

DE 2:

JobID  |  CustomerID  |    Link1      |   Link 2     |    Link3     |
---------------------------------------------------------------------
 123   |    122       | http://link1  | http://link2 | http://link3 |

 332   |    56        | http://link1  | http://link2 |              |

Is there anyone who have experienced similar task ?

1 Answer 1

2

You can't use the newer T-SQL pivot functions in SFMC, but you can do it this way:

select
jobid
, customerid
, max(case when link = 'http://link1' then link else '' end) as Link1
, max(case when link = 'http://link2' then link else '' end) as Link2
, max(case when link = 'http://link3' then link else '' end) as Link3
FROM DE1
group by 
jobid
, customerid

Update

If you don't want to pick them by value, you can assign a number to each column with a windowing function and use that to pick. You can change the order by to order the rows to whatever fits your criteria for assigning values to the columns.

select
 x.jobid
,x.customerid
,max(case when x.num = 1 then link else '' end) as Link1
,max(case when x.num = 2 then link else '' end) as Link2
,max(case when x.num = 3 then link else '' end) as Link3
FROM (
  select
   d.jobid
  ,d.customerid
  ,d.link
  ,row_number() over(partition by d.jobid, d.subscriberkey order by newid()) num
  from de1 d
) as x 
where x.num <= 3
group by 
 x.jobid
,x.customerid
6
  • The link **http://link1, http://link2** etc. is dynamic and is different for every user, which means I can't predefined the value of "name". It would be good if I could print the value of the column incrementally.
    – M. Kyorov
    Commented Jul 13, 2017 at 14:44
  • Outside of SQL, how would you determine which link goes in which column for a customer? Commented Jul 13, 2017 at 14:53
  • With a Script you can do a loop. Get the first link if exist put it in column Link1, get second link put it in column Link2. Is there a way you can do something similar in SQL ?
    – M. Kyorov
    Commented Jul 13, 2017 at 15:14
  • That's similar to my second example. The order by controls the order of the rows by jobid and customerid. Commented Jul 13, 2017 at 15:55
  • No this is not solving the problem. Where does "value" come from ? I don't have such a field and you are not able to print column cell value by writing "value".
    – M. Kyorov
    Commented Jul 18, 2017 at 12:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .