Single row and column result for multi-row column

Question

I have a function in SQL that returns a single row in a single column with records separated by points.

Example:

Column 1: Result

Line 1: 0000000.0000001.0000002.0000003.0000004.0000005.0000006

I would like to insert each value in a separate row, but in a single column.

Example:

Column 1: Result

Line 1: 0000000

Row 2: 0000001

Row 3: 0000002

Line 4: 0000003

Line 5: 0000004

Row 6: 0000005

Line 7: 0000006

Summary, I need my result of a column and a row to turn into a column with several lines, but in a dynamic way, because the number of records separated by points (.) may vary, that is, I can have 2 records separated by points, which will be 2 lines, or 1000 records, which will be 1000 lines.

How can I proceed?

Answer 1

Felipe, your request contains two of the most frequently asked questions on SQL forums: (1) break a character string containing several fields separated by a delimiter (split string) and (2) turn "one row / multiple columns" into "multiple rows / one column" (unpivot).

---

About the first part, split string, there are several interesting articles on the subject, in which you will find various solutions to the problem. I suggest reading article "Separating multi-valued text content (split string)”.

---

On the second part, unpivot, here are articles on the subject:

• Use SQL Server’s UNPIVOT Operator to help normalize output
• Use SQL Server’s UNPIVOT Operator to dynamically normalize output

---

For demonstration was used the Splitstrings_moden function, which is derived from a function created by Jeff Moden. It performs the split string and also the unpivot.

Code #1 generates a temporary table (table variable) to simulate the data. The call of the split string was made in the FROM clause (using cross apply), as it is a function of the type table-Valued.

-- código #1
declare @Retorno table (ID int identity, Resultado varchar(max));

INSERT into @Retorno (Resultado) values
  ('0000000.0000001.0000002.0000003.0000004.0000005.0000006'),
  (replicate('8888888.',400) + '0000000'),
  ('0123456');

SELECT T.ID, U.Item 
  from @Retorno as T
       cross apply dbo.splitStrings_Moden(T.Resultado,'.') as U;

Below, the code transcription of the Splitstrings_moden function.

-- código #2
CREATE FUNCTION dbo.SplitStrings_Moden
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLEfaz
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;
go

Answer 2

If I understand correctly, you want to make a row become a column with multiple lines, right?

This is called pivoting (pivoting) in a database and can be defined as showing data that is stored in line to be shown in columns.

If this is the case, in SQL Server you can use a dynamic query to not have to list all fields (values of the rows that will be converted into column titles) manually:

use seu_banco_de_dados;

declare @colunas as nvarchar(max),
        @query as nvarchar(max);

select @colunas = stuff((select ',' + quotename(coluna_escolhida) 
                      from dbo.sua_tabela
                      group by coluna_escolhida
                      order by coluna_escolhida
                  for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')

set @query = 'select ' + @colunas + ' from 
             (
                select valores, coluna_escolhida
                from dbo.TbFinanciamentos
            ) x
            pivot 
            (
                max(valores)
                for coluna_escolhida in (' + @colunas + ')
            ) p '

execute(@query)

--exec sp_executesql @query;