Need PIVOT or other query help for SQL Server
I have a table tbl_M_CDS_Curves with the following fields:
Market_Date (Date), Curve_Name (varchar), Fixed_Term (int), Spread (real).
I want to query the value of spread for each unique curve_name and fixed_term for two dates, Date1 and Date2 and get this kind of result:
Curve_Name1, Fixed_Term1, Spread on Date1, Spread on Date2
Curve_Name1, Fixed_Term2, Spread on Date1, Spread on Date2
Curve_Name1, Fixed_Term3, Spread on Date1, Spread on Date2
Curve_Name1, Fixed_TermN, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_Term1, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_Term2, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_Term3, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_TermN, Spread on Date1, Spread on Date2
There is only one row in the table for each date, curve_name and fixed_term.
The documentation on PIVOT is just too abstract for me. Any help will be most appreciated!
We are running SQL Server 2008.
Market_Date (Date), Curve_Name (varchar), Fixed_Term (int), Spread (real).
I want to query the value of spread for each unique curve_name and fixed_term for two dates, Date1 and Date2 and get this kind of result:
Curve_Name1, Fixed_Term1, Spread on Date1, Spread on Date2
Curve_Name1, Fixed_Term2, Spread on Date1, Spread on Date2
Curve_Name1, Fixed_Term3, Spread on Date1, Spread on Date2
Curve_Name1, Fixed_TermN, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_Term1, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_Term2, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_Term3, Spread on Date1, Spread on Date2
Curve_Name2, Fixed_TermN, Spread on Date1, Spread on Date2
There is only one row in the table for each date, curve_name and fixed_term.
The documentation on PIVOT is just too abstract for me. Any help will be most appreciated!
We are running SQL Server 2008.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it! You just had an extra ( with ((. Also, my field is Curve, not Curve_Name, it turns out, so the correct query is
SELECT A.Curve, A.Fixed_Term, A.Spread [First_Date], B.Spread [Second_Date]
FROM (SELECT Curve, Fixed_Term, Spread FROM tbl_M_CDS_Curves WHERE Market_Date = '2014-12-31') AS A
LEFT JOIN (SELECT Curve, Fixed_Term, Spread FROM tbl_M_CDS_Curves WHERE Market_Date = '2015-01-02') AS B
ON A.Curve = B.Curve AND A.Fixed_Term = B.Fixed_Term
ORDER BY A.Curve, A.Fixed_Term
SELECT A.Curve, A.Fixed_Term, A.Spread [First_Date], B.Spread [Second_Date]
FROM (SELECT Curve, Fixed_Term, Spread FROM tbl_M_CDS_Curves WHERE Market_Date = '2014-12-31') AS A
LEFT JOIN (SELECT Curve, Fixed_Term, Spread FROM tbl_M_CDS_Curves WHERE Market_Date = '2015-01-02') AS B
ON A.Curve = B.Curve AND A.Fixed_Term = B.Fixed_Term
ORDER BY A.Curve, A.Fixed_Term
ASKER
Apparently, I didn't need a pivot at all. Thanks!
ASKER
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ON'.