CODEWITHSUNDEEP
sqlt-sqlsql-server-2012pivot
Nils
Nils

Reputation: 514

SQL Pivot row to column?

My table looks like this

enter image description here

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

enter image description here

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).

enter image description here

Upvotes: 0

Views: 133

Answers (1)

john McTighe
john McTighe

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

Related Questions