Forum Discussion
Matrix Twist
I have a customer who uses a spreastsheet to track inventory.
It looks like this:
| ID | Attr1 | Attr2 | Attr3 | v1 | v2 | v1 | v2 | v1 | v2 |
| 1 | a | b | a | 1 | x | 2 | y | 3 | z |
| 1 | a | b | b | 4 | x | 5 | y | 6 | z |
| 1 | a | b | c | 7 | x | 8 | x | 9 | z |
As you can see it appears to be pivoted, but instead of being pivoted by the ID, it is pivoted by ID, and Attr 1-3.
Now they are moving towards an online system that stores the data like this:
| ID | Attr1 | Attr2 | Attr3 | V1 | V2 |
| 1 | a | b | a | 1 | x |
| 1 | a | b | a | 2 | y |
| 1 | a | b | a | 3 | z |
| 1 | a | b | b | 4 | x |
| 1 | a | b | b | 5 | y |
| 1 | a | b | b | 6 | z |
| 1 | a | b | c | 7 | x |
| 1 | a | b | c | 8 | y |
| 1 | a | b | c | 9 | z |
But they still want the report to look like their spreadsheet.
Normally a Matrix would be just the thing but, a Matrix results in the mutiple anchors arranged hierarchacally,
+ID V1 v2 v1 v2 v1 v2
+Attr1
+ Attr3
Attr3
Which they hate.
It occurred to me to do a multi column pivot in T-SQL and then just throw the result into a regular table, but T-SQL seems to insist that you know the the pivoted values, V1 and V2 ahead of time and essentially hard code them. In real life, this usually is not the case (and I never understood why they do that). Also, doing this in SQL is somethign I never really understood or mastered.
So, I am looking for suggestions and alternative.
Wax Poetic!
Thanks.
2 Replies
Depending on the information you provide, you can put it directly in a table visual, or you want to achieve something else, can you be more specific, from the information you give it is not difficult to understand what the final effect you want to look like.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
