Swap rows for columns

Question

Hi, I have the following query:

set NOCOUNT on;

declare @_mes int = 2
declare @_ano int = 2016

declare @_dataini  datetime = CONVERT(DATETIME, '01/' + REPLICATE('0', 2 - LEN(@_MES)) + CAST(@_MES AS VARCHAR(2)) + '/' + CAST(@_ANO AS VARCHAR(4)), 103)
declare @_datafim  datetime = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @_dataini)+1,0))
declare @_dataaux  datetime
declare @_cnpj_cpf varchar(14)
declare @_razao    varchar(100)
declare @_fantasia varchar(100)
declare @_colunas  varchar(max) = ''
declare @_comando varchar(max) = ''

exec('drop table #TEMPSCANN')
CREATE TABLE #TEMPSCANN (CNPJ_CPF VARCHAR(14), RAZAO VARCHAR(100) , FANTASIA VARCHAR(100))
select @_dataaux = @_dataini

While (@_dataaux <= @_datafim)
begin
    DECLARE @_coma as varchar(100) = 'alter table #TEMPSCANN add [' + convert(varchar(10), @_dataaux, 103) + '] DATETIME'
    exec(@_coma)
    select @_dataaux = DATEADD(DAY, 1, @_dataaux)
end

DECLARE scann_cursor CURSOR FOR 
SELECT C.CNPJ_CPF, C.RAZAO, C.FANTASIA
FROM CLIENTES C, GWADMCLI ADM, GWITMADMCLI ITM
WHERE  C.CODCLIE = ADM.CODCLIE
   AND ADM.CODADMCLI = ITM.CODADMCLI
   AND ITM.CODPROD = 1365
   AND ITM.SITUACAO IN ('G','C')
   AND ADM.CONTRATO IN ('G','S')
   AND C.ATIVO = 'S'
ORDER BY C.CNPJ_CPF;

OPEN scann_cursor

FETCH NEXT FROM scann_cursor 
INTO @_cnpj_cpf, @_razao, @_fantasia

WHILE @@FETCH_STATUS = 0
BEGIN
    select @_colunas = ''
    select @_comando = ''
    select @_dataaux = @_dataini

    select @_comando = 'INSERT INTO #TEMPSCANN (CNPJ_CPF, RAZAO, FANTASIA) VALUES ( ' + char(39) + @_cnpj_cpf + char(39) +','+ char(39) + @_razao + char(39) +','+ char(39) +  @_fantasia + char(39) + ')'
    exec(@_comando)

    while (@_dataaux <= @_datafim)
    begin
        select @_colunas = @_colunas + ', [' + convert(varchar(10), @_dataaux, 103) + '] '
        select @_dataaux = DATEADD(DAY, 1, @_dataaux)
    end

    -- Get the next vendor.
    FETCH NEXT FROM scann_cursor 
    INTO @_cnpj_cpf, @_razao, @_fantasia
END
CLOSE scann_cursor;
DEALLOCATE scann_cursor;

select * from #TEMPSCANN

Her return is:

CNPJ_CPF    RAZAO   FANTASIA    01/02/2016  02/02/2016  03/02/2016  04/02/2016  05/02/2016  06/02/2016  07/02/2016  08/02/2016  09/02/2016  10/02/2016  11/02/2016  12/02/2016  13/02/2016  14/02/2016  15/02/2016  16/02/2016  17/02/2016  18/02/2016  19/02/2016  20/02/2016  21/02/2016  22/02/2016  23/02/2016  24/02/2016  25/02/2016  26/02/2016  27/02/2016  28/02/2016  29/02/2016

In it I can list every day of the month I want, I would now like to popular this temporary table with the data (QUANTITY on the correct day), as I could finish this?

Answer 1

You’ll have to do something this way.

   DECLARE @_MES INT = 1
DECLARE @_ANO INT = 2016

DECLARE @_DATAINI AS DATETIME = CONVERT(DATETIME, '01/' + REPLICATE('0', 2 - LEN(@_MES)) + CAST(@_MES AS VARCHAR(2)) + '/' + CAST(@_ANO AS VARCHAR(4)), 103)
DECLARE @_DATAFIM AS DATETIME = DATEADD(S, -1, DATEADD(MM, DATEDIFF(M, 0, @_DATAINI)+1,0))


declare @tabela table 
(
    CNPJ_CPF  VARCHAR(1000) ,
    DATA date,
    QUANTIDADE int
)


insert into @tabela values
('052.213.258.23', '02/01/2016', 1),
('052.333.258.23', '03/01/2016', 2),
('222.213.258.23', '04/01/2016', 2),
('333.213.258.23', '01/01/2016', 4),
('555.213.258.23', '06/01/2016', 5),
('111.213.258.23', '05/01/2016', 6),
('052.213.333.23', '08/01/2016', 7),
('052.213.444.23', '09/01/2016', 14),
('545.213.258.23', '10/01/2016', 15)


DECLARE @_COLUNAS AS VARCHAR(1000) = ''

DECLARE @_DATAAUX AS DATETIME = @_DATAINI
WHILE (@_DATAAUX <= @_DATAFIM)
BEGIN
    SELECT @_COLUNAS = @_COLUNAS + ', 0 [' + CONVERT(VARCHAR(10), @_DATAAUX, 103) + ']'
    SELECT @_DATAAUX = DATEADD(DAY, 1, @_DATAAUX)
END
EXEC ('DROP TABLE ##TableTempScanntech')
DECLARE @_comando as varchar(2000) = ' SELECT REPLICATE(' + CHAR(39) + ' ' + CHAR(39) + ', 14) CNPJ_CPF ' + @_COLUNAS + ' INTO ##TableTempScanntech'
EXEC (@_comando)
EXEC (' TRUNCATE TABLE ##TableTempScanntech')

DECLARE @sql nvarchar(MAX)

ALTER TABLE tempdb..##TableTempScanntech ALTER COLUMN CNPJ_CPF  VARCHAR(1000) NULL 

SELECT @sql = COALESCE(@sql+'ALTER TABLE tempdb..##TableTempScanntech ALTER COLUMN ['+ Name+'] int NULL ', ' ')
FROM tempdb.sys.columns  c
WHERE object_id = object_id('tempdb..##TableTempScanntech')
EXECUTE (@sql)

set @sql = '';

SELECT   @sql = COALESCE(@sql+'insert into ##tabletempScanntech ([CNPJ_CPF] ,' + quotename(name)+ ') values ( ''' +  cast(t.CNPJ_CPF as  VARCHAR(1000) ) + ''', ''' +  cast(t.QUANTIDADE as varchar) + ''' )','')
FROM tempdb.sys.columns  c
join @tabela t
on Convert(varchar(10),CONVERT(date,DATA,106),103) = c.name
WHERE object_id = object_id('tempdb..##TableTempScanntech') 
EXECUTE (@sql)
select * from ##tabletempScanntech

In this case you are creating a temporary table without any Insert so you have only the fields that are the days of the months, so you have to retrieve the column name as follows.

SELECT Name FROM tempdb.sys.columns  c WHERE object_id = object_id('tempdb..##TableTempScanntech')

With this you have the possibility to compare the date of your other table with the name of your column, detail check the field type of your table and the format, see that in this case I am converting as follows.

Convert(varchar(10),CONVERT(date,DATA,106),103)

Since date is of the DATE type, it is in the format dd/mm/yyyy as its columns are

Well, I just couldn’t understand why you’re not allowing CNPJ_CPF to be in the field, and I’m a little out of time right now, but the idea is around.

Try an update

If your LOGSCANNTECH table had more than one record per CNPJ_CPF with different time you would need to have a Count of Dates like this.

declare @temp table
(
  CNPJ_CPF varchar(1000),
  DATA date
)

insert into @temp 
select CNPJ_CPF, count(DATA) as total , DATA from
(
    select CNPJ_CPF, cast(DATA as date)  as DATA from LOGSCANNTECH 
)d
group by CNPJ_CPF, DATA


update c
set c.name = lc.QUANTIDADE  --- = total 
from #TEMPSCANN tc
join tempdb.sys.columns  c
on object_id = object_id('tempdb..##TEMPSCANN') 
join @temp lc
on lc.CNPJ_CPF = tc.CNPJ_CPF
and  Convert(varchar(10),CONVERT(date,lc.DATA,106),103) = c.name