none
Tranpor valores de linhas para colunas RRS feed

  • Pergunta

  • Olá

    Preciso de ajuda para transpor valores de uma tabela que está formatada da seguinte forma:

    Nome       DataHora                       Categoria     cod

    Rotina1     01/01/2014 10:30             Iniciada       100

    Rotina2     01/01/2014 10:30             Iniciada       100

    Rotina1     01/01/2014 10:40            Concluida     200

    Rotina1     01/01/2014 10:50             Iniciada       100

    Rotina2     01/01/2014 10:51            Concluida     200

    Rotina3     01/01/2014 10:30             Iniciada       100

    Mas preciso transpor os dados para que fique assim:

    Nome       Inicio                            Conclusao     

    Rotina1     01/01/2014 10:30          01/01/2014 10:40  

    Rotina2     01/01/2014 10:30          01/01/2014 10:51

    quinta-feira, 8 de janeiro de 2015 22:21

Respostas

  • Experimente essa nova versão:

    declare @Tabela table
    (Rotinas varchar(1000), DataHora datetime, Categoria varchar(15), cod int);
    
    insert into @Tabela values
    ('"O Agendador de Tarefas concluiu com êxito a instância ""{12345678-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:20', 'Concluida', 200),
    ('"O Agendador de Tarefas iniciou a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:30', 'Iniciada', 100),
    ('"O Agendador de Tarefas concluiu com êxito a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:51', 'Concluida', 200);
    
    with 
        CTE_Substring as
        (
            select
                SUBSTRING
                    (Rotinas, 
                     PATINDEX('%\%', Rotinas), 
                     PATINDEX('%"" para%', Rotinas) - PATINDEX('%\%', Rotinas)) as Rotinas,
                SUBSTRING
                    (Rotinas, 
                     PATINDEX('%{%', Rotinas) + 1, 
                     PATINDEX('%}%', Rotinas) - PATINDEX('%{%', Rotinas) - 1) as Chave,
                Categoria,
                DataHora
            from @Tabela
        )
    
    select
        i.Rotinas,
        i.Chave,
        i.DataHora as Inicio,
        c.DataHora as Conclusao
    from CTE_Substring as i
    inner join CTE_Substring as c
        on 
            c.Rotinas = i.Rotinas and
            c.Chave = i.Chave 
    where
        i.Categoria = 'Iniciada' and
        c.Categoria = 'Concluida'

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br


    • Editado gapimex terça-feira, 13 de janeiro de 2015 11:52
    • Marcado como Resposta Druduit terça-feira, 13 de janeiro de 2015 16:35
    terça-feira, 13 de janeiro de 2015 11:51

Todas as Respostas

  • Boa noite,

    Experimente mais ou menos dessa forma:

    with CTE_RN as
    (
        select
            *,
            ROW_NUMBER() OVER(PARTITION BY Nome, Categoria ORDER BY DataHora) as RN
        from Tabela
    )
    
    select
        i.Nome,
        i.DataHora as Inicio,
        c.DataHora as Conclusao
    from CTE_RN as i
    inner join CTE_RN as c
        on 
            c.Nome = i.Nome and
            c.RN = i.RN 
    where
        i.Categoria = 'Iniciada' and
        c.Categoria = 'Concluida'

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 8 de janeiro de 2015 23:11
  • Boa noite amigo,

    desculpe, falta código?

    não tenho muito conhecimento, com o código, não retornou erro, mas não listou nada.

    Obrigado.

    quinta-feira, 8 de janeiro de 2015 23:47
  • Boa noite,

    Segue abaixo uma solução.

    select
    	a.Nome
    	,a.DataHora as Inicio	
    	,b.DataHora as Conclusao
    from (select Nome,DataHora from teste where categoria = 'Iniciada') a
    	left join (select Nome,DataHora from teste where categoria = 'Concluida') b
    		on a.Nome = b.Nome
    group by 
    	a.Nome
    	,a.DataHora 
    	,b.DataHora 


    sexta-feira, 9 de janeiro de 2015 04:29
  • Solução 3

    select
    	a.Nome
    	,a.DataHora as Inicio	
    	,teste2.DataHora as Conclusao
    from teste a
    	cross apply  
    		(
    			select *
    			from teste b
    			where b.nome = a.nome
    		) as teste2
    where a.categoria = 'Iniciada'
    	and teste2.categoria = 'Concluida'

    sexta-feira, 9 de janeiro de 2015 04:50
  • Druduit, a query que sugeri está completa e deveria retornar as linhas cujo Nome possui pelo menos um "início" e uma "conclusão".

    Se a mesma não retornou nada, acredito que será necessário adaptar os valores das condições da clausula Where conforme os valores da sua tabela.


    Assinatura: http://www.imoveisemexposicao.com.br

    sexta-feira, 9 de janeiro de 2015 12:11
  • Boa Noite Junior,

    não retorna nada, pois ele está validando se tiver as duas condições (iniciada e concluída).

    sexta-feira, 9 de janeiro de 2015 20:18
  • Para melhor compreensão, estou tentando importar os logs do event viewer  com apenas as informações de inicio e fim de tarefas agendadas no agendador de tarefas.

    preciso depois disto gerar um relatório de desempenho da tasks.

    Por isso preciso do inicio da execução e do fim da execução, em todos as vezes que iniciou e concluiu.

    assim em cada execução poderei mensurar o tempo de execução.

    De cada uma das tarefas agendadas.

    se houver uma outra forma podemos tentar.

    desde já agradeço.

    sexta-feira, 9 de janeiro de 2015 20:22
  • Poderia anexar em um arquivo texto 10 linhas do LOG do Event Viewer, não deve estar retornando nada pois os valores na coluna Categoria devem estar diferentes do que a especificada no código

    Atenciosamente,

    Rafael Durbano

    sábado, 10 de janeiro de 2015 02:18
  • Blz Rafael,

    é um log personalizado, somente inicio e fim da rotina.

     
    segunda-feira, 12 de janeiro de 2015 03:12
  • Druduit, experimente fazer um teste com o script abaixo através do SQL Server Management Studio:

    declare @Tabela table
    (Nome varchar(15), DataHora datetime, Categoria varchar(15), cod int);
    
    insert into @Tabela values
    ('Rotina1', '01/01/2014 10:30', 'Iniciada', 100),
    ('Rotina2', '01/01/2014 10:30', 'Iniciada', 100),
    ('Rotina1', '01/01/2014 10:40', 'Concluida', 200),
    ('Rotina1', '01/01/2014 10:50', 'Iniciada', 100),
    ('Rotina2', '01/01/2014 10:51', 'Concluida', 200),
    ('Rotina3', '01/01/2014 10:30', 'Iniciada', 100);
    
    with CTE_RN as
    (
        select
            *,
            ROW_NUMBER() OVER(PARTITION BY Nome, Categoria ORDER BY DataHora) as RN
        from @Tabela
    )
    
    select
        i.Nome,
        i.DataHora as Inicio,
        c.DataHora as Conclusao
    from CTE_RN as i
    inner join CTE_RN as c
        on 
            c.Nome = i.Nome and
            c.RN = i.RN 
    where
        i.Categoria = 'Iniciada' and
        c.Categoria = 'Concluida'

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 12 de janeiro de 2015 11:47
  • Boa tarde, funcionou.

    Porém no meu código não funcionou pelo seguinte:

    Como é um log de evento da tarefa, no campo onde registra o nome registra outras informações:

    Tarefa Iniciada

     "O Agendador de Tarefas iniciou a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."

    Tarefa concluída

     "O Agendador de Tarefas concluiu com êxito a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."

     Precisaria então montar a tabela com apenas o nome de cada task - neste exemplo seria: Adobe Flash Player Updater

    consigo com o cod abaixo fazer isso, mas não estou conseguindo juntar ao seu script. não sei se é a melhor forma.

    select substring(Rotinas, (PATINDEX ('%\%', Rotinas)),
      ((PATINDEX ('%"" para%', Rotinas)) - (PATINDEX ('%\%', Rotinas)) ))as Rotinas, DataHora

    se eu conseguisse capturar os logs por power shell por exemplo filtrando apenas o nome da task , já resolveria tbem.


    • Editado Druduit segunda-feira, 12 de janeiro de 2015 18:23
    segunda-feira, 12 de janeiro de 2015 17:29
  • Experimente fazer um teste com a versão abaixo:

    declare @Tabela table
    (Rotinas varchar(1000), DataHora datetime, Categoria varchar(15), cod int);
    
    insert into @Tabela values
    ('"O Agendador de Tarefas iniciou a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:30', 'Iniciada', 100),
    ('"O Agendador de Tarefas concluiu com êxito a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:51', 'Concluida', 200);
    
    with 
        CTE_Substring as
        (
            select
                substring
                    (Rotinas, 
                     PATINDEX('%\%', Rotinas), 
                     PATINDEX('%"" para%', Rotinas) - PATINDEX('%\%', Rotinas)) as Rotinas,
                Categoria,
                DataHora
            from @Tabela
        ),
        
        CTE_RN as
        (
            select
                *,
                ROW_NUMBER() OVER(PARTITION BY Rotinas, Categoria ORDER BY DataHora) as RN
            from CTE_Substring
        )
    
    select
        i.Rotinas,
        i.DataHora as Inicio,
        c.DataHora as Conclusao
    from CTE_RN as i
    inner join CTE_RN as c
        on 
            c.Rotinas = i.Rotinas and
            c.RN = i.RN 
    where
        i.Categoria = 'Iniciada' and
        c.Categoria = 'Concluida'

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 12 de janeiro de 2015 18:04
  • Funcionou!!

    apenas um detalhe:

    Quando o log começa com "Tarefa Concluída"  as datas de conclusão fica errada

    para resolver adicionei

    and

    .DataHora <= c.DataHora

    mas daí não aparece nenhuma informação sobre essa rotina.

    em cada inicio de execução da task existe uma chave no log por exemplo: {9B370BDF-A2A0-4942-B473-76710C4A1A01}

    que será a mesma quando esta task terminar. quando inicar novamente a mesma task muda de chave ate aproxima finalização.

    poderia usar esse valor para filtra se há um inicio e fim.


    • Editado Druduit terça-feira, 13 de janeiro de 2015 00:29
    segunda-feira, 12 de janeiro de 2015 19:18
  • Experimente essa nova versão:

    declare @Tabela table
    (Rotinas varchar(1000), DataHora datetime, Categoria varchar(15), cod int);
    
    insert into @Tabela values
    ('"O Agendador de Tarefas concluiu com êxito a instância ""{12345678-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:20', 'Concluida', 200),
    ('"O Agendador de Tarefas iniciou a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:30', 'Iniciada', 100),
    ('"O Agendador de Tarefas concluiu com êxito a instância ""{54B5A101-2491-49FB-BA18-644F87CA2ADE}"" da tarefa ""\Adobe Flash Player Updater"" para o usuário ""Home\PC-DIEGO$""."', '01/01/2014 10:51', 'Concluida', 200);
    
    with 
        CTE_Substring as
        (
            select
                SUBSTRING
                    (Rotinas, 
                     PATINDEX('%\%', Rotinas), 
                     PATINDEX('%"" para%', Rotinas) - PATINDEX('%\%', Rotinas)) as Rotinas,
                SUBSTRING
                    (Rotinas, 
                     PATINDEX('%{%', Rotinas) + 1, 
                     PATINDEX('%}%', Rotinas) - PATINDEX('%{%', Rotinas) - 1) as Chave,
                Categoria,
                DataHora
            from @Tabela
        )
    
    select
        i.Rotinas,
        i.Chave,
        i.DataHora as Inicio,
        c.DataHora as Conclusao
    from CTE_Substring as i
    inner join CTE_Substring as c
        on 
            c.Rotinas = i.Rotinas and
            c.Chave = i.Chave 
    where
        i.Categoria = 'Iniciada' and
        c.Categoria = 'Concluida'

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br


    • Editado gapimex terça-feira, 13 de janeiro de 2015 11:52
    • Marcado como Resposta Druduit terça-feira, 13 de janeiro de 2015 16:35
    terça-feira, 13 de janeiro de 2015 11:51
  • Boa tarde gamipex

    Exatamente isso. Muito obrigado pela força!!

    terça-feira, 13 de janeiro de 2015 16:37
  •    em um dos servidores que extrai o arquivo de logs, o formato datahora está assim 1/15/2015 2:15:32 PM

    há como importar para o banco convertendo para (15/01/2015 14:15:32) ?

    a coluna data da Tabela esta formatada como Datetime2(0).

    sexta-feira, 16 de janeiro de 2015 19:41
  • Como está sendo feita a importação?

    Dependendo do caso pode ser alterada a configuração de DateFormat:

    set dateformat mdy;
    declare @Data datetime;
    set @Data = '1/15/2015 2:15:32 PM';
    select @Data

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    sexta-feira, 16 de janeiro de 2015 23:17
  • Boa Noite gapimex,

    funcionou a conversão de data.

     

    estou usando como forma de importação o script:

    bulk insert dbo.Table_table1
    from 'C:\Users\Usuario\Documents\Operação\Rotinas\server1\log.txt'
    WITH
    (KEEPNULLS,
    FIRSTROW = 4,
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n')

     se o extraio manualmente do event viewer consigo importar normalmente.

    porém estou testando uma forma de extrair automaticamente por powershell, e consigo gerar o log personalizado porem não consigo importar com o mesmo script

     

    Get-WinEvent -LogName Microsoft-Windows-TaskScheduler/Operational | where {($_.ID -eq 100) -OR ($_.ID -eq 102)} | Format-Table -Wrap -AutoSize -Property LevelDisplayName, TimeCreated, ProviderName, Id, TaskDisplayName, Message | Out-String -Width 4096 | out-file C:\Users\usuario\Documents\Operação\Rotinas\logs\logs.txt

    o erro no sql:

    Mensagem 4866, Nvel 16, Estado 8, Linha 1

    Falha no carregamento em massa. A coluna  longa demais no arquivo de dados, na linha 1, coluna 1. Verifique se o terminador de campo e da linha esto especificados corretamente.

    Mensagem 7301, Nvel 16, Estado 2, Linha 1

    No  possvel obter a interface necessria ("IID_IColumnsInfo") do provedor do OLE DB "BULK" para o servidor vinculado "(null)".

    grato pela atenção!

     

    quarta-feira, 21 de janeiro de 2015 00:02
  • Desculpe mas não tenho nenhuma sugestão sobre esse erro.

    Se você não receber nenhuma sugestão, acredito que seja melhor você criar um outro tópico para essa questão.

    Abs


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 21 de janeiro de 2015 12:04