SQL query to Transpose columns into rows

Question

can somebody help with a query for below problem

Return Table

Id     Factor  monthlyReturns  price date

A1     0.2     0.001           2010-01-29
A1     0.2     0.003           2010-02-26
A1     0.2     0.004           2010-03-31
A1     0.2     0.004           2010-04-30
A2     0.1     0.001           2010-01-29
A2     0.1     0.001           2010-02-26
A2     0.1     0.001           2010-03-31
A2     0.1     0.001           2010-04-30 
A3     0.3     0.03            2010-01-29 
A3     0.3     0.04            2010-02-26
A3     0.3     0.05            2010-03-31
A3     0.3     0.05            2010-04-30
A4     0.4     0.12            2010-01-29
A4     0.4     0.12            2010-02-26
A4     0.4     0.14            2010-03-31
A4     0.4     0.15            2010-04-30

I want to convert this data into following format .Can somebody please suggest a query

            A1       A2      A3      A4      Total
31-Jan-10   0.001   0.001   0.03    0.12     0.001*0.2(Factor for  A1)+0.001*0.1(Factor forA2)+
                                             0.03*0.3(Factor forA3)+ 0.12*0.4(Factor forA4)

28-Feb-10   0.003   0.001   0.04    0.12     Same as above
31-Mar-10   0.004   0.001   0.05    0.14     Same as above
30-Apr-10   0.004   0.001   0.05    0.15     Same as above

Thanks

PIVOT is the keyword you're looking for. But you have to KNOW the values if not, you'll have to use dynamic SQL. Several examples exist in stack already: http://stackoverflow.com/questions/16417484/tsql-complex-pivot for one. — xQbert, Dec 22 '14 at 20:06
There is no 31-Jan-10 in the input data. How come such a date is produced in the output data? — Giorgos Betsos, Dec 22 '14 at 20:35
@Giorgos - Thats raw data in the table however on the report the month end date is represented..So that can be ignored. — user1143726, Dec 22 '14 at 20:42

score 1 · Accepted Answer · edited Dec 22 '14 at 21:15

below is the query you asked for, notice that you need to change the table name to yours.

a little explanation: I assumed the Ids are limited to A1,A2,A3,A4. otherwise this will not work and you will need a more complexed solution.

first i created a common table expression of a pivoted table with all the factors grouped by date (assuming that there can not be two factors to the same id on the same date)

then, i join that with a pivoted table that holds all the monthly returns.

note: i used the sum function on the fields A1,A2,.. since i needed to group it by date and the select can only use aggregate function if the column is not in the group by clause. this does not effect the values since you only have one row for each id on a particular date, but correct me if i'm wrong.

with FactorsByDate(PriceDate,FactorA1,FactorA2,FactorA3,FactorA4)
AS
(
  SELECT [price date] AS Date, 
  SUM([A1]) AS A1, SUM([A2]) AS A2, SUM([A3]) AS A3, SUM([A4]) AS A4
  FROM
  (SELECT Id, Factor , [price date]
   FROM TempTbl) AS SourceTable
   PIVOT
   (
     SUM(Factor)
     FOR Id IN ([A1], [A2], [A3], [A4])
   ) AS PivotTable
   group by [price date]
)
SELECT Date,A1,A2,A3,A4, A1*FactorA1 + A2*FactorA2 + A3*FactorA3 + A4*FactorA4 AS Total
FROM
(
  SELECT [price date] AS Date, 
  SUM([A1]) AS A1, SUM([A2]) AS A2, SUM([A3]) AS A3, SUM([A4]) AS A4
  FROM
  (SELECT Id, monthlyReturns , [price date]
   FROM TempTbl) AS SourceTable
   PIVOT
   (
     SUM(monthlyReturns)
     FOR Id IN ([A1], [A2], [A3], [A4])
   ) AS PivotTable
   group by [price date]
  ) AS monthlyReturns
INNER JOIN FactorsByDate ON FactorsByDate.PriceDate = monthlyReturns.Date

SQL query to Transpose columns into rows

1 Answers1