Is it possible to make a direct SPLIT in a QUERY (SELECT) in SQL SERVER 2012?

Question

I have the following table below and I need to divide the "Full Name" column into columns in the same SELECT where each column should be filled according to its level.

Ex: The Full Name column should return in each new column what refers to its level separated by ' ', but the HIGHEST and LOWEST level should not change column according to what I demonstrate in the excel below. Does anyone have any idea how to do this?? I did a lot of research on the internet, but only found functions that should be used in procedures.

The level ranging from 0 to 3 (4 levels) ...

4 LEVELS = LEVEL 3 :

MAIOR   |    NIVEL 2   |  NIVEL 1  |   NIVEL 0

3 LEVELS = LEVEL 2

MAIOR   |   NULL    |  NIVEL1   |   NIVEL 0 

2 LEVELS = LEVEL 1

MAIOR  |   NULL   |  NULL    |  NIVEL 0

Answer 1

To separate columns that have several values in the same row the usual is to use functions of the type string split. Even from version 2016 there is the native function STRING_SPLIT. However, when the separation of values is required to be returned in the same order, the native function STRING_SPLIT does not guarantee.

In your case, 2012 version of SQL Server, I suggest you use the Delimitedsplit8k function, authored by Jeff Moden. Additional information about the function is in the article "Separating multi-valued text content”.

Here is the solution that uses the Delimitedsplit8k function:

-- código #1
with Separado as (
SELECT id, max (nome) as nome, max (Nivel) as Nivel, 
    [I1]= max (case when SS.ItemNumber = 1 then SS.Item end),
    [I2]= max (case when SS.ItemNumber = 2 then SS.Item end),
    [I3]= max (case when SS.ItemNumber = 3 then SS.Item end),
    [I4]= max (case when SS.ItemNumber = 4 then SS.Item end)
  from tabela as T1
       cross apply dbo.DelimitedSplit8K (T1.NomeCompleto, '\') as SS
  --where ...
  group by id
)
SELECT id, nome, Nivel,
       I1 as NomeMaiorNivel,
       case when Nivel = 3 then I2 else '' end as NomeNivel2,
       case when Nivel >= 2 then I3 else '' end as NomeNivel1,
       case when Nivel >= 1 then I4 else '' end as NomeNivel0
  from Separado;

I have not tested; may contain error(s).

---

Still in the same article there are other solutions. Here is the adaptation of the solution proposed in item 3.4 to your case:

-- código #2
with Separado as (
SELECT id, nome, Nivel,
       convert (XML,
                '<r><n>' + replace (NomeCompleto, '\', '</n><n>') 
                + '</n></r>'
               ) as Item
  from dbo.tabela
)
SELECT id, nome, Nivel,
       i.value('n[1]', 'varchar(20)') as NomeMaiorNivel,
       case when Nivel = 3 then i.value('n[2]', 'varchar(20)') else '' end as NomeNivel2,
       case when Nivel >= 2 then i.value('n[3]', 'varchar(20)') else '' end as NomeNivel1,
       case when Nivel >= 1 then i.value('n[4]', 'varchar(20)') else '' end as NomeNivel0
  from Separado as SX
       cross apply SX.Item.nodes('/r') Item(i);

I have not tested; may contain error(s).