Treat a column of the same table twice and present as a record in the same column

Question

Good afternoon Dear Friends,

I am developing a report with some necessary standards, I will not delve into the standards for the query, but fact is that I "managed" to make a Query with the necessary requirements through concatenation, which was so:

SELECT 
'0101010' + STUFF('000000000000000', 14-len(P.CODIGO_PESSOA), 14, P.CODIGO_PESSOA) + '    02'+STUFF('                                                  ',1,len(P.NOME), P.NOME) +'0701'+

'

0201010' + STUFF('000000000000000', 14-len(P.CODIGO_PESSOA), 14, 
P.CODIGO_PESSOA)  + '    0200103'+ @DT_ATUAL +'0933333'

From bd.PESSOAS P

Where P.CODIGO_PESSOA <> ''

order by 
P.NOME

This report needs to be exported to a TXT file and the database result is perfect as it presents the information exactly as accurate, requiring only "copy" to the TXT:

The problem occurs because as I did through concatenation, obviously the result of the "two lines" (I am considering the result after the line break as another line) are in the same record.

When I take this Query to the report of our system, which only allows us to export reports to EXCEL causing that when copying the data from the exported spreadsheet to a TXT, the line break is lost, since it was inserted through "formatting".

The result I need is that the line before the break (+'0701'+

') be a record and the row after the break be another record, all in the same "column" in this way:

That’s possible?

Thank you so much for your attention.

Answer 1

Here is a possible solution (there may be some syntax error, because I wrote without access to SSMS).

DECLARE @DT_ATUAL VARCHAR = CONVERT(VARCHAR, GETDATE(), 104)

;WITH PESSOAS AS 
(
    SELECT 'Aaron John Doe' AS NOME,   '112' AS CODIGO_PESSOA UNION ALL
    SELECT 'Abigail Jane Doe' AS NOME, '222' AS CODIGO_PESSOA
),
RELATORIO AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY P.NOME) AS ROW_ID, 
            '0101010' + RIGHT(REPLICATE('0', 15) + P.CODIGO_PESSOA, 15) + '    02' + LEFT(P.NOME + REPLICATE(' ', 50), 50) + '0701' AS LINHA1,
            '0201010' + RIGHT(REPLICATE('0', 15) + P.CODIGO_PESSOA, 15) + '    0200103' + @DT_ATUAL + '0933333' AS LINHA2
       FROM PESSOAS P
      WHERE P.CODIGO_PESSOA <> ''
 )
 SELECT ROW_ID,
        LINHA1 AS REGISTO
   FROM RELATORIO 
  UNION ALL
 SELECT ROW_ID,
        LINHA2
   FROM RELATORIO
  ORDER BY 1, 2

The result is as follows:

ROW_ID  REGISTO
1       0101010000000000000112    02Aaron John Doe                                    0701
1       0201010000000000000112    020010300933333
2       0101010000000000000222    02Abigail Jane Doe                                  0701
2       0201010000000000000222    020010300933333

I also made some changes that should help make the query more readable - replace the STUFF for RIGHT+REPLICATE ou LEFT+REPLICATE

It should make it even easier, because the only thing that needs to be guaranteed is the order of the records. The column ROW_ID, added only to help in sorting, can be easily removed from the final result.

Here is another alternative, creating the two lines per register. A CTE Pessoas is only used to create some test data.

DECLARE @DT_ATUAL VARCHAR = CONVERT(VARCHAR, GETDATE(), 104)

;WITH PESSOAS AS 
(
    SELECT 'Aaron John Doe' AS NOME,   '112' AS CODIGO_PESSOA UNION ALL
    SELECT 'Abigail Jane Doe' AS NOME, '222' AS CODIGO_PESSOA
),
RELATORIO AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY P.NOME, P.CODIGO_PESSOA) AS ROW_ID, 
                '0101010' + RIGHT(REPLICATE('0', 15) + P.CODIGO_PESSOA, 15) + '    02' + LEFT(P.NOME + REPLICATE(' ', 50), 50) + '0701' AS REGISTO
      FROM PESSOAS P
     WHERE P.CODIGO_PESSOA <> ''

    UNION ALL

    SELECT ROW_NUMBER() OVER (ORDER BY P.NOME, P.CODIGO_PESSOA) AS ROW_ID, 
                '0201010' + RIGHT(REPLICATE('0', 15) + P.CODIGO_PESSOA, 15) + '    0200103' + @DT_ATUAL + '0933333'
      FROM PESSOAS P
     WHERE P.CODIGO_PESSOA <> ''
)
SELECT REGISTO
  FROM RELATORIO
 ORDER BY ROW_ID, REGISTO

The result without the column ROW_ID is

0101010000000000000112    02Aaron John Doe                                    0701
0201010000000000000112    020010300933333
0101010000000000000222    02Abigail Jane Doe                                  0701
0201010000000000000222    020010300933333