changing rows group to column

I have a sql 开发者_Go百科query result with following structure and data

ProductId #date      #customercode  # SomeIndex    # SomeIndexvalue
13       2011-10-01     1           A1              9397062 
14       2011-10-01     2           A1              3575045 
15       2011-10-01     3           A1             3575321  
13       2011-10-01     1           A2             1318774
14       2011-10-01     2           A2             838924
15       2011-10-01     3           A2             8942430  

In this index value is unique for productid, date, customercode. Now i need to make SomeIndex as column so that in the given data there will be only three rows. i.e, the following structure

ProductId #date      #customercode  # A1      # A2
13       2011-10-01     1           9397062   1318774
14       2011-10-01     2           3575045   838924
15       2011-10-01     3           3575321   8942430

I am using sqlserver 2008. Can anyone help me out in this. Thanks in advance everyone

With the PIVOT operator (SQL 2008 version)

create table #test(
ProductId int,
dt date,
customercode int,
someindex varchar(255),
SomeIndexvalue int
)

insert into #test values
(13,'2011-10-01',1,'A1',9397062),
(14,'2011-10-01',2,'A1',3575045),
(15,'2011-10-01',3,'A1',3575321),
(13,'2011-10-01',1,'A2',1318774),
(14,'2011-10-01',2,'A2',838924),
(15,'2011-10-01',3,'A2',8942430)


select * from 
(
   select 
      * 
   from  
      #test
)DATA_TO_PIVOT
PIVOT
(
MAX(SomeIndexvalue) for someindex in ([A1],[A2])
)PIVOTED_DATA      

SELECT ProductId, [#date], [#customercode],
       MIN(CASE WHEN [#SomeIndex] = 'A1' THEN [#SomeIndexvalue] END) [#A1],
       MIN(CASE WHEN [#SomeIndex] = 'A2' THEN [#SomeIndexvalue] END) [#A2]
FROM YourTable
GROUP BY ProductId, [#date], [#customercode]