SELECT * FROM (SELECT lt.[Transaction_Date], lt.[Equipment_Number], lt.[Lube_Type], lt.[Quantity] FROM dbo.EquipmentMaster em INNER JOIN dbo.LubeTransactions lt ON em.Equipment_Number = lt.Equipment_Number WHERE lt.[Transaction_Date] Between '12/1/09' AND '12/31/09' AND lt.[Lube_Type] Like '%85%' Or lt.[Lube_Type] Like '%Grease%' And Report_Classification = '99' ) tbl PIVOT (SUM([Quantity]) FOR [Lube_Type] IN ([Grease], [W85])) pvt ORDER BY [Equipment_Number]
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM (SELECT lt.[Transaction_Date], lt.[Equipment_Number], lt.[Lube_Type], lt.[Quantity] FROM dbo.EquipmentMaster em INNER JOIN dbo.LubeTransactions lt ON em.Equipment_Number = lt.Equipment_Number WHERE lt.[Transaction_Date] Between '12/1/09' AND '12/31/09' AND lt.[Lube_Type] Like '%85%' Or lt.[Lube_Type] Like '%Grease%' And Report_Classification = '99' ) tbl PIVOT (SUM([Quantity]) FOR [Lube_Type] IN ([Grease], [W85])) pvt ORDER BY [Equipment_Number]";
cmd.CommandType = CommandType.Text;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//do your logic part here
}
reader.Close();
conn.Close();
}
SELECT * FROM (SELECT lt.[Transaction_Date], lt.[Equipment_Number], lt.[Lube_Type], lt.[Quantity]
FROM EquipmentMaster em INNER JOIN LubeTransactions lt ON em.Equipment_Number = lt.Equipment_Number
WHERE lt.[Transaction_Date] Between '12/01/09' and '12/31/09' AND lt.[Lube_Type] Like '85% ' Or lt.[Lube_Type] Like '%Grease% ' AND em.Report_Classification = '99 ' tbl
PIVOT (SUM([Quantity]) FOR [Lube_Type] IN ([W85], [Grease])) pvt
ORDER BY [Equipment_Number]