Formatting data from management database

Question

I've got some data that goes like this:

    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 

and I need it to be in this format:

    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      

the tabs get messed up on here but hopefully you get my drift. does anyone know an easy way to do this? I haven't found one with excel just yet.

Answer 1

ended up going for the SQL route and using this code:

    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