Single line and column result for multi-line column [closed]

Felipe, your request contains two of the most frequently asked questions in SQL forums: ( 1 ) breaking a string that has multiple fields separated by a split < string ) and 2 ) transform "one row / multiple columns" into "multiple rows / one column" ( unpivot ).

On the first part, split string , there are several interesting articles on the subject, in which you will find several solutions to the problem. The author of the articles, Aaron Bertrand, is one of the current SQL Server gurus .

• Split strings the right way - or the next best way
• Splitting Strings: A Follow-Up
• Splitting Strings: Now with less T-SQL
• Comparing string splitting / concatenation methods
• Performance Surprises and Assumptions: STRING_SPLIT ()

As of the 2016 version of SQL Server, the String_split () function is available.

About 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

To demonstrate the function SplitStrings_Moden, proposed in the first reference, "Split strings the right way - or the next best way". It performs the split string and also the unpivot .

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

-- 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 is the transcript of the SplitStrings_Moden function code.

-- 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

If I understand correctly, you want to make a row become a multi-row column, right?

This is called a pivoting database and can be defined as showing the data that is stored on the line being displayed 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 rows that will be converted to column headings) 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;