Forum Discussion
Solved
Convert data in rows to columns with attribute index
I need to convert data from a format where each row contains different data based on an attribute column into a proper table with the attribute column entries as new column headers.
Tried a few things without luck - like to have the done in query editor - any ideas?
| accountId | ATID | Value |
| 1000004 | gp_dc_database_id | 5 |
| 1000004 | gp_dc_department_id | 26142 |
| 1000004 | gl_account | 5040 |
| 1000004 | gl_account_index | 26142-5040 |
| 1000004 | gp_customer_id | 00117 |
| 1000005 | gp_dc_database_id | |
| 1000005 | gp_dc_department_id | 26142 |
| 1000005 | gl_account | 5040 |
| 1000005 | gl_account_index | 26142-5040 |
| 1000005 | gp_customer_id | 00009 |
| 1000006 | gp_dc_database_id | 5 |
| 1000006 | gp_dc_department_id | 26142 |
| 1000006 | gl_account | 5040 |
| 1000006 | gl_account_index | 26142-5040 |
| 1000006 | gp_customer_id | 00114 |
| 1000007 | gp_dc_database_id | 5 |
| 1000007 | gp_dc_department_id | 26142 |
| 1000007 | gl_account | 5040 |
| 1000007 | gl_account_index | 26142-5040 |
| 1000007 | gp_customer_id | 00017 |
| 1000008 | gp_dc_database_id | 5 |
| 1000008 | gp_dc_department_id | 26142 |
| 1000008 | gl_account | 5040 |
| 1000008 | gl_account_index | 26142-5040 |
| 1000008 | gp_customer_id | 00120 |
Desired
| AccountID | gp_dc_database_id | gp_dc_department_id | gl_account | gl_account_index | gp_customer_id |
| 1000004 | 5 | 26142 | 5040 | 26142-5040 | 00117 |
| 1000005 | 26142 | 5040 | 26142-5040 | 00009 | |
| 1000006 | 5 | 26142 | 5040 | 26142-5040 | 00114 |
| 1000007 | 5 | 26142 | 5040 | 26142-5040 | 00017 |
| 1000008 | 5 | 26142 | 5040 | 26142-5040 | 00120 |
2 Replies
Yep, that worked - I tried multiple times to pivot , concatenate etc etc - must be tired:)
Most appreciated!
