---- 0) Tabulka:
---- 1) Pivot:
---- 2a) Dodajme Vcelku:
---- 2b) Dodajme Vcelku s odstranenim NULL Vcelku:
---- 2c) Dodajme Vcelku s uplnym odstranenim NULL (+nazvy stlpcov):
---- 3a) Bez pivot:
---- 3b) Bez pivot: SUM(CASE ...

USE tempdb;
GO

---- 0) Tabulka:
----
if OBJECT_ID('#T1', 'U') IS NOT NULL DROP TABLE #T1
CREATE TABLE #T1(Oddel Char, Rok SMALLINT, Kvartal TINYINT, Obrat DECIMAL(2,1))
GO
 INSERT INTO #T1 (Oddel, Rok, Kvartal, Obrat)
 SELECT 'A', 2006, 1, 0.6 UNION ALL
 SELECT 'B', 2006, 1, 0.7 UNION ALL
 SELECT 'A', 2006, 3, 0.9 UNION ALL
 SELECT 'B', 2006, 3, 0.7 UNION ALL
 SELECT 'A', 2006, 4, 0.8 UNION ALL
 SELECT 'B', 2006, 4, 0.8 UNION ALL
 SELECT 'A', 2007, 1, 0.7 UNION ALL
 SELECT 'A', 2007, 2, 0.9 UNION ALL
 SELECT 'A', 2007, 2, 0.9 UNION ALL
 SELECT 'A', 2007, 3, 0.8 UNION ALL
 SELECT 'A', 2007, 3, 0.6 UNION ALL
 SELECT 'A', 2007, 4, 0.9 UNION ALL
 SELECT 'B', 2007, 4, 0.7;
GO
----SELECT * FROM #T1

---- 1) Pivot:
--SELECT * FROM
--		(SELECT Rok, Kvartal, Obrat FROM #T1)  pom
--  PIVOT (SUM(Obrat) FOR Kvartal IN ([1],[2],[3],[4])) piv
--  ORDER BY Rok

---- alebo namiesto * 
---- vsimnime si, ze na kvartali sa odvolavame cez ich hodnoty:

--SELECT Rok, [1],[2],[3],[4] FROM
--		(SELECT Rok, Kvartal, Obrat FROM #T1)  pom
--  PIVOT (SUM(Obrat) FOR Kvartal IN ([1],[2],[3],[4])) piv
--  ORDER BY Rok

---- 2a) Dodajme Vcelku:
--SELECT Rok, [1],[2],[3],[4], [1]+[2]+[3]+[4] Vcelku FROM
--		(SELECT Rok, Kvartal, Obrat FROM #T1)  pom
--  PIVOT (SUM(Obrat) FOR Kvartal IN ([1],[2],[3],[4])) piv
--  ORDER BY Rok

---- 2b) Dodajme Vcelku s odstranenim NULL Vcelku:
--SELECT Rok, [1],[2],[3],[4],
-- COALESCE([1],0)+ COALESCE([2],0) + COALESCE([3],0) + COALESCE([4],0)
-- Vcelku FROM
--		(SELECT Rok, Kvartal, Obrat FROM #T1)  pom
--  PIVOT (SUM(Obrat) FOR Kvartal IN ([1],[2],[3],[4])) piv
--  ORDER BY Rok

---- 2c) Dodajme Vcelku s uplnym odstranenim NULL (+nazvy stlpcov):
SELECT Rok,
 COALESCE([1],0) [1], COALESCE([2],0) [2],  COALESCE([3],0)[3],  COALESCE([4],0)[4],
 COALESCE([1],0)+ COALESCE([2],0) + COALESCE([3],0) + COALESCE([4],0)
 Vcelku FROM
		(SELECT Rok, Kvartal, Obrat FROM #T1)  pom
  PIVOT (SUM(Obrat) FOR Kvartal IN ([1],[2],[3],[4])) piv
  ORDER BY Rok

---- 3a) Bez pivot:
--SELECT Oddel, Rok, SUM(Obrat) AS Vcelku FROM #T1
--  GROUP BY Oddel, Rok
--  ORDER BY Rok

---- 3b) Bez pivot:
SELECT Rok,
        SUM(CASE WHEN Kvartal = 1 THEN Obrat ELSE 0 END) [1],
        SUM(CASE WHEN Kvartal = 2 THEN Obrat ELSE 0 END) [2],
        SUM(CASE WHEN Kvartal = 3 THEN Obrat ELSE 0 END) [3],
        SUM(CASE WHEN Kvartal = 4 THEN Obrat ELSE 0 END) [4],
        SUM(Obrat) AS Vcelku
   FROM #T1
GROUP BY Rok


---------------------------------------
---- CHECKSUM computes a hash value, called the checksum, over its list of arguments. 
--SELECT TOP 100 RAND(), NEWID(), CHECKSUM(NEWID()),
--		RAND(CHECKSUM(NEWID())) FROM Master.sys.SysColumns t1