У меня есть такие данные:
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.
в итоге выбрал маршрут 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