Showing values as a string of text

[jonclay]

Hi everyone

I have a field in my SQL database that shows a field called Type_ with a set of numbers separated by a comma for several million record e.g.

Is it possible to add a column / measure that says "if the field Type_ contains 15742000 then show this as A and if the field contains / also contains 154720004 then show this as B)? So, in this example we would show A, B in stead of 15472000, 154720004. There are many of these numbers and therefore literally thousands of combinations, otherwise I would create a lookup table and link that.

Any help would be much appreciated.

Many thanks
Jon

[smpa01]

@jonclay   what is the RDMBMS? Is it MSSQL? 

If there is no filter context involved in this, I would strongly suggest you to do this transformation server side for a million row table. An example in TSQL with a lookup table

DECLARE @fact AS TABLE
  (
     rownum INT,
     descr  VARCHAR (40)
  )

INSERT INTO @fact
SELECT *
FROM   (VALUES(1,
       'email,phone'),
              (2,
       'email,phone,pager'),
              (3,
       'fax,computer')) t(a, b)

DECLARE @lookup AS TABLE
  (
     _name    VARCHAR(10),
     _altname VARCHAR(10)
  )

INSERT INTO @lookup
SELECT *
FROM   (VALUES('email',
       'e'),
              ('phone',
       'p1'),
              ('pager',
       'p2'),
              ('fax',
       'f'),
              ('computer',
       'c')) t(a, b);

WITH cte1
     AS (SELECT rownum,
                x.value AS _splitValue,
                z._altname
         FROM   @fact
                CROSS apply String_split(descr, ',')x
                CROSS apply (SELECT y.*
                             FROM   @lookup y
                             WHERE  x.value = y._name)z)
SELECT a1.*,
       x.plc
FROM   @fact a1
       CROSS apply(SELECT *
                   FROM  (SELECT rownum,
                                 Stuff((SELECT ',' + _altname
                                        FROM   cte1 a
                                        WHERE  a.rownum = b.rownum
                                        FOR xml path ('')), 1, 1, '') AS plc
                          FROM   cte1 b
                          GROUP  BY rownum) a2
                   WHERE  a1.rownum = a2.rownum) x 

From here

to

[harshnathani · Accepted solution]

Hi @jonclay ,

This may not be the best way. But you can try something like this.

Create a Calculated Column

Column = SUBSTITUTE(

SUBSTITUTE(Table4[Type],"154720004","B"),"15472000","A")

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[jonclay]

@harshnathani Thanks so much, this works perfectly!

[parry2k]

@jonclay in this example your new value will be B since all the rows contain both the values, correct?

[jonclay]

@parry2k Many thanks for your reply. No, the field can contain any number of different values e.g. 15470000, 15470001, 15470002 and so on.