0

I tried to achieve the following task but the query output was showing only the first values

Query

SELECT SUBSTRING([Location], 1, CASE CHARINDEX('OR', [OFFER_CODE])
        WHEN 0
            THEN LEN([Location])
        ELSE CHARINDEX('OR', [Location]) - 1
        END) AS Location , SubKey
FROM XYZtable

Output :

enter image description here

Eg: Initial Data extension

enter image description here

Final Data Extension

enter image description here

Please suggest how to populate the remaining values in data extension

1 Answer 1

1

This isn't a SFMC-specific question per se, but SFMC doesn't support the standard T-SQL pivot & unpivot functions. The workaround solutions are always tricky, cumbersome and a bunch of other adjectives.

Here's how I'd do it. There may be a simpler way, but I was able to get a model working based on some other pivot queries I've written for SFMC:

select
  ca.sk SubKey
, ca.loc Location
from (

  SELECT
      x.SubKey
    , x.location
    , SUBSTRING(x.location, 1, ISNULL(p1 - 1, LEN(x.location))) location1
    , SUBSTRING(x.location, p1 + 1, ISNULL(p2, LEN(x.location) + 1) - p1 - 1) location2
    , SUBSTRING(x.location, p2 + 1, ISNULL(p3, LEN(x.location) + 1) - p2 - 1) location3
    , SUBSTRING(x.location, p3 + 1, ISNULL(p4, LEN(x.location) + 1) - p3 - 1) location4
    , SUBSTRING(x.location, p4 + 1, ISNULL(p5, LEN(x.location) + 1) - p4 - 1) location5
    , SUBSTRING(x.location, p5 + 1, ISNULL(p6, LEN(x.location) + 1) - p5 - 1) location6
    , SUBSTRING(x.location, p6 + 1, ISNULL(p7, LEN(x.location) + 1) - p6 - 1) location7
  from (

    /* fabricated data for example */
          select SubKey = '1', location = replace('NY',' OR ','|')
    union select SubKey = '2', location = replace('Austria',' OR ','|')
    union select SubKey = '3', location = replace('NY OR US OR UK',' OR ','|')
    union select SubKey = '4', location = replace('Austria OR Germany',' OR ','|')

    /* include actual query here*/
    /*
    select 
      de.subKey
    , replace(de.location,' OR ', '|') location
    from YOURDATAEXTENSION de
    where 1=1
    */

  ) x
  CROSS APPLY (select NULLIF(CHARINDEX('|', x.location), 0)) b(p1)
  CROSS APPLY (select NULLIF(CHARINDEX('|', x.location, p1 + 1), 0)) c(p2)
  CROSS APPLY (select NULLIF(CHARINDEX('|', x.location, p2 + 1), 0)) d(p3)
  CROSS APPLY (select NULLIF(CHARINDEX('|', x.location, p3 + 1), 0)) e(p4)
  CROSS APPLY (select NULLIF(CHARINDEX('|', x.location, p4 + 1), 0)) f(p5)
  CROSS APPLY (select NULLIF(CHARINDEX('|', x.location, p5 + 1), 0)) g(p6)
  CROSS APPLY (select NULLIF(CHARINDEX('|', x.location, p6 + 1), 0)) h(p7)
  where isnull(x.location,'') != ''

) y
cross apply (
  values 
      (y.subkey, y.location1)
    , (y.subkey, y.location2)
    , (y.subkey, y.location3)
    , (y.subkey, y.location4)
    , (y.subkey, y.location5)
    , (y.subkey, y.location6)
    , (y.subkey, y.location6)
) ca (sk,loc)
where isnull(ca.loc,'') != ''

You'll notice I manufactured your test cases in the innermost query. You'll need to replace that with a query from the actual data extension containing the data.

Also I replaced ' OR ' with '|' just for simplicity in the substrings. Dealing with a single character as a delimiter instead of a 4 characters (2 spaces and 2 characters) is simpler.

Results

|SubKey | Location |
|:----- |:---------|
|1      | NY       |
|2      | Austria  |
|3      | NY       |
|3      | US       |
|3      | UK       |
|4      | Austria  |
|4      | Germany  |

If you'd like to try it, here's a SQLFiddle.

IMPORTANT: After you've got this working, you can go back to whoever's sourcing the data like this and ask them to normalize it. The fact that you're getting it delimited like this, means there's technical debt to pay off.

You must log in to answer this question.

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