11

I am trying to pivot on multiple columns. I am using SQL server 2008. Here is what I have tried so far

CREATE TABLE #t ( id int, Rscd varchar(10),Accd varchar(10),position int)

INSERT INTO #t Values (10,'A','B',1)

INSERT INTO #t Values (10,'C','D',2)

Select id,[1],[2],[11],[12] FROM
(SELECT id, Rscd,Accd, position , position +10 as Aposition 
From #t)
As query
PIVOT (MAX(Rscd )
      FOR Position IN ([1],[2])) AS Pivot1
      PIVOT (MAX(Accd )
      FOR Aposition IN ([11],[12])) AS Pivot2

The below indicated is the result that I am getting

id  1     2     11    12
10  NULL  C     NULL  D
10  A     NULL  B     NULL

But the result that I am trying to achieve is ,

id  1   2   11   12
10  A   C   B    D

Any help ? what is wrong in my code.

Taryn
  • 242,637
  • 56
  • 362
  • 405
user1005310
  • 737
  • 2
  • 12
  • 40
  • possible duplicate of [Multiple Column Pivot in T-SQL](http://stackoverflow.com/questions/947281/multiple-column-pivot-in-t-sql) – Ryan Gates Mar 12 '14 at 17:41

3 Answers3

15

I would unpivot the columns into pairs first, then pivot them. Basically the unpivot process will convert the pairs of columns (rscd, position and accd, aposition) into rows, then you can apply the pivot. The code will be:

select id, [1], [2], [11], [12]
from
(
  select id, col, value
  from #t
  cross apply
  (
    select rscd, position union all
    select Accd, position + 10
  ) c (value, col)
) d
pivot
(
  max(value)
  for col in ([1], [2], [11], [12])
) piv;

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 1
    Bluefeet - Can we aggregate by multiple columns in pivot ? Eg. max(value), min(col1), sum(col5). If yes, is there a good example for that ? – Trojan.ZBOT Jan 22 '14 at 22:19
  • 2
    @Trojan.ZBOT There might be other ways to get the final result besides using pivot. If you have a specific question, then I would post one to get the best answer...it would be easier than going back and forth in the comments. :) – Taryn Jan 22 '14 at 22:20
  • 1
    I am a bit concerned about downvoters. But, can we have pivot ( max(value), min(col1), sum(col5) for col in ([1], [2], [11], [12]) ) piv; – Trojan.ZBOT Jan 22 '14 at 22:21
  • 1
    If you formulate a good question with table structure, sample data, desired result and even include samples of what you tried, then you shouldn't worry about downvotes. No, you can't pivot on multiple columns that way. – Taryn Jan 22 '14 at 22:23
  • okay, but before I prepare it can you tell if we can have a pivot like this - pivot ( max(value), min(col1), sum(col5) for col in ([1], [2], [11], [12]) ) piv; ? – Trojan.ZBOT Jan 22 '14 at 22:24
  • 1
    No, you can't pivot on multiple columns that way – Taryn Jan 22 '14 at 22:24
  • Thanks. Does multiple aggregates even make sense ? – Trojan.ZBOT Jan 22 '14 at 22:25
  • It really depends on what you want to do. Post a new question and see what the responses are. – Taryn Jan 22 '14 at 22:26
  • I cannot think of a scenario where multiple aggregates could be needed. So, I don't know how I can make that question. Would this be an ok SO question - Can we have multiple aggregate conditions in pivot ? If yes, is there an example for the same ? – Trojan.ZBOT Jan 22 '14 at 22:30
  • @Trojan.ZBOT No, that question would be off-topic. Questions must demonstrate a problem with code and attempt at solving that problem. – Kermit Jan 22 '14 at 22:36
  • @FreshPrinceOfSO - Can you answer the question in my comment ? I just want to know if it is possible or not. – Trojan.ZBOT Jan 22 '14 at 22:39
  • @Trojan.ZBOT I don't think that question would be a good fit but you can always try to come up with a sample of data and then the final desired result to ask it. I'm not sure what else I could say about it until I see a question with some details. – Taryn Jan 22 '14 at 22:46
  • well, as per http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx you can have only ONE column for aggregation. – Trojan.ZBOT Jan 22 '14 at 23:06
  • @Trojan.ZBOT As I said they might be **alternate** ways to do this, but I can't keep guessing. – Taryn Jan 23 '14 at 13:42
5
Select id,sum([1]),sum([2]),sum([11]),sum([12]) FROM
(SELECT id, Rscd,Accd, position , position +10 as Aposition 
From #t)
As query
PIVOT (MAX(Rscd )
      FOR Position IN ([1],[2])) AS Pivot1
      PIVOT (MAX(Accd )
      FOR Aposition IN ([11],[12])) AS Pivot2

group by id
CRABOLO
  • 8,605
  • 39
  • 41
  • 68
rimenri
  • 51
  • 1
  • 1
-3

Dont Use the ID Column. Use a derived table to retrieve all columns except the ID and then use the PIVOT table.

Sumant
  • 1