☰

格式化管理数据库的数据

tag-icon • tag-icon database database-administration configuration-management
格式化管理数据库的数据

我有一些如下数据：

    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

我需要它采用以下格式：

    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

这里的标签很乱，但希望你能明白我的意思。有人知道一个简单的方法来做到这一点吗？我还没有找到一个使用 excel 的方法。

答案1

最终选择了 SQL 路线并使用了以下代码：

    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

相关内容