Reputation: 514
My table looks like this
i want to pivot the rows into table heads.
if i use:
SELECT [USER],[DIVISION], [COMPANY],[RIGHTS] FROM
(SELECT [USER],[ATTRIBUTE],[VALUE],[RIGHTS] FROM [AGDA_MANUAL].[dbo].[TEST] WHERE [PROC] = 'FINANCE' ) AS T1
PIVOT (MAX([VALUE]) FOR [ATTRIBUTE] IN ([DIVISION],[COMPANY])) AS T2
And this is almost what i want
but is there a way to NOT use the MAX in the pivot? im loosing DIVSION 010 AND COMPANY 1 for DOMAIN\USER1 when i use the MAX ??.
can any one help me here?
****UPDATE*************
I was missing one column i needed because i was going to end up with more rows(as one of you said).
Upvotes: 0
Views: 133
Reputation: 1181
The problem is that pivot needs some way to connect the DIVISION and COMPANY data.
MAX allows it to do so - otherwise how does it know which COMPANY is connected with which DIVISION.
One solution to this would be to add an artificial column (Generated via RowNumber()
) that you would add to the grouping (along with PROC & USER)
You would still need to decide if DIV 023 belongs with COMP 1,2 or 3
You could end up with something like
FINANCE | DOMAIN\USER1 | 1 | DIVISION | 023
FINANCE | DOMAIN\USER1 | 2 | DIVISION | 010
FINANCE | DOMAIN\USER1 | 1 | COMPANY | 1
FINANCE | DOMAIN\USER1 | 2 | COMPANY | 2
FINANCE | DOMAIN\USER2 | 1 | COMPANY | 3
Now you can pivot and have Company & DIVISION as columns on the Same Row
Upvotes: 1