Smash125 (10/12/2012)

---

SELECT
YEAR(OrderDate) AS OrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q2
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY 1 ASC

Using above Query i got below report
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 5850932.9483 8476619.278
2002 7379686.3091 8210285.1655 13458206.13 10827327.4904
2003 8550831.8702 10749269.374 18220131.5285 16787382.3141
2004 14170982.5455 17969750.9487 56178.9223 NULL

output should like this

OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 58,50932.9483 84,76619.278
2002 73,79686.3091 82,10285.1655 13,458206.13 10,827327.4904
2003 85,50831.8702 10,749269.374 18,220131.5285 16,787382.3141
2004 14,170982.5455 17,969750.9487 56,178.9223 NULL

SELECT OrderYear, 
CONVERT(VARCHAR(100), CAST(Q1 AS money), 1), 
CONVERT(VARCHAR(100), CAST(Q2 AS money), 1), 
CONVERT(VARCHAR(100), CAST(Q3 AS money), 1), 
CONVERT(VARCHAR(100), CAST(Q4 AS money), 1)
FROM (SELECT 
      YEAR(OrderDate) AS OrderYear,
      SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
      SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
      SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
      SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q4
      FROM Sales.SalesOrderHeader
      GROUP BY YEAR(OrderDate)
      ) a
ORDER BY OrderYear;

Cadavre (10/12/2012)

---

If you insist on doing it in the database layer, it can be achieved like this:

Jeff Moden (10/12/2012)

---

Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.

Jeff Moden (10/12/2012)

---

Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.

Vedran Kesegic (10/12/2012)

---

Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.

Vedran Kesegic (10/12/2012)

---

Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.

ChrisM@home (10/14/2012)

---

Lakhs instead of thousands.

L' Eomot Inversé (10/14/2012)

---

Maybe, but if so there seems to be also a rule that for amounts less than 1 lakh a comma eparates thousands: this is shown by the last number, where the sated form is 56,178.9223.

56,178.9223     -- 3

58,50932.9483	-- 5 
84,76619.278	-- 5 
73,79686.3091	-- 5  
82,10285.1655	-- 5  
85,50831.8702	-- 5 
 
10,749269.374	-- 6  
18,220131.5285	-- 6 
16,787382.3141	-- 6
14,170982.5455	-- 6 
17,969750.9487	-- 6 
13,458206.13	-- 6 
10,827327.4904	-- 6