Formatando dados do banco de dados de gerenciamento

Question

Eu tenho alguns dados que são assim:

    Config_Name Question    Answer
    Cisco WAN   Sensitivity:    High
    Cisco WAN   Authorized Users:   Brent, Charles
    Cisco WAN   Last Audited:   n/a
    Cisco WAN   Next Audit: 3/30/2012
    Cisco WAN   Audit Signature:    
    Cisco WAN   Username:   MYCOMPANY
    Cisco WAN   Password:   
    Cisco WAN   Encrypted-A ENCRYPTED DATA
    Cisco WAN   Encrypted-B 
    Cisco WAN   Encrypted-C 
    vCenter server  Sensitivity:    High
    vCenter server  Authorized Users:   Brent, Charles
    vCenter server  Last Audited:   
    vCenter server  Next Audit: 3/30/2012
    vCenter server  Audit Signature:    ENCRYPTED DATA
    vCenter server  Username:   administrator
    vCenter server  Password:   
    vCenter server  Encrypted-A ENCRYPTED DATA
    vCenter server  Encrypted-B 
    vCenter server  Encrypted-C 
    AKSC-NE01 IPMI  Sensitivity:    High
    AKSC-NE01 IPMI  Authorized Users:   Brent, Charles
    AKSC-NE01 IPMI  Last Audited:   
    AKSC-NE01 IPMI  Next Audit: 3/30/2012
    AKSC-NE01 IPMI  Audit Signature:    ENCRYPTED DATA
    AKSC-NE01 IPMI  Username:   MYCOMPANY
    AKSC-NE01 IPMI  Password:   
    AKSC-NE01 IPMI  Encrypted-A ENCRYPTED DATA
    AKSC-NE01 IPMI  Encrypted-B 
    AKSC-NE01 IPMI  Encrypted-C 

e eu preciso que esteja neste formato:

    Config_Name Sensitivity:    Authorized Users:   Last Audited:   Next Audit: Audit Signature:    Username:   Password:   Encrypted-A Encrypted-B Encrypted-C
    AKSC-NE01 IPMI  High    Brent, Charles      3/30/2012   ENCRYPTED DATA  MYCOMPANY       ENCRYPTED DATA      
    Cisco ASA5505 WAN   High    Brent, Charles  n/a 3/30/2012   ENCRYPTED DATA  MYCOMPANY       ENCRYPTED DATA      
    vCenter server  High    Brent, Charles      3/30/2012   ENCRYPTED DATA  administrator       ENCRYPTED DATA      

as abas ficam confusas aqui, mas espero que você me entenda. Alguém sabe uma maneira fácil de fazer isso? Ainda não encontrei nenhum com o excel.

    

Answer 1

acabou indo para a rota SQL e usando este código:

    Select t.Config_Name,   
          MAX(CASE When t.Seq_Nbr = '1.00'  then t.Answer ELSE NULL END) as Sensitivity,
          MAX(CASE When t.Seq_Nbr = '2.00'  then t.Answer ELSE NULL END) as AuthorizedUsers,
          MAX(CASE When t.Seq_Nbr = '3.00'  then t.Answer ELSE NULL END) as LastAudited,
          MAX(CASE When t.Seq_Nbr = '4.00'  then t.Answer ELSE NULL END) as NextAudit,
          MAX(CASE When t.Seq_Nbr = '5.00'  then t.Answer ELSE NULL END) as AuditSig,
          MAX(CASE When t.Seq_Nbr = '6.00'  then t.Answer ELSE NULL END) as Username,
          MAX(CASE When t.Seq_Nbr = '7.00'  then t.Answer ELSE NULL END) as 'Password',
          MAX(CASE When t.Seq_Nbr = '7.50'  then t.Answer ELSE NULL END) as 'Sum',
          MAX(CASE When t.Seq_Nbr = '8.00'  then t.Answer ELSE NULL END) as 'Enc-A',
          MAX(CASE When t.Seq_Nbr = '9.00'  then t.Answer ELSE NULL END) as 'Enc-B',
          MAX(CASE When t.Seq_Nbr = '10.00' then t.Answer ELSE NULL END) as 'Enc-C'
      from [sql table] t    
      Where Config_Type = 'Credential'  
      Group By Config_Name