Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
This is coming from a sql server, I should have thought of this earlier, but I'm currently looking into 'joins' –  bVector Apr 5 '12 at 19:02

1 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  
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.