MS SQL ile çapraz sorguda pivot tablo satır ve sutün toplamı alma
7.316 defa okundu
Konu: Veri Analizi
İçindekiler
SQL ile yapılan pivot tablolara satır ve sütunlara toplamı eklemek istenirse aşağıdaki sorgudan yaralanılabilir.
Bunun için öncelikle örnek geçici tablo oluşturuldu. Pivot tabloya satır ve sütun eklemek için 3 sorgu oluşturuldu ve 3 sorgu birleştirildi.
1.Sorgu pivot oluşturan sorgudur.
2.Sorgu sütün toplamı almaktadır ve şehir anahtarı ilk sorgu sonucuna bağlanmıştır.
3.Sorgu ise alt toplam satırı oluşturulmuş ve union ile tablonun en altına eklenmiştir.
Geçici tablo oluşturma
- SELECT 'ANKARA' AS SEHIR,'2015-02-10' AS TARIH,10 TUTAR
- INTO #TABLO
- UNION
- SELECT 'İZMİR' AS SEHIR,'2015-04-18' AS TARIH,20 TUTAR
- UNION
- SELECT 'ANKARA' AS SEHIR,'2015-01-22' AS TARIH,40 TUTAR
- UNION
- SELECT 'İSTANBUL' AS SEHIR,'2015-03-15' AS TARIH,30 TUTAR
- UNION
- SELECT 'İZMİR' AS SEHIR,'2015-07-11' AS TARIH,50 TUTAR
- UNION
- SELECT 'ANKARA' AS SEHIR,'2015-06-28' AS TARIH,20 TUTAR
- UNION
- SELECT 'ANKARA' AS SEHIR,'2015-04-04' AS TARIH,10 TUTAR
- UNION
- SELECT 'İZMİR' AS SEHIR,'2015-03-17' AS TARIH,50 TUTAR
- UNION
- SELECT 'İSTANBUL' AS SEHIR,'2015-05-09' AS TARIH,40 TUTAR
- UNION
- SELECT 'İSTANBUL' AS SEHIR,'2015-01-30' AS TARIH,70 TUTAR
- UNION
- SELECT 'ANKARA' AS SEHIR,'2015-01-24' AS TARIH,70 TUTAR
- UNION
- SELECT 'İZMİR' AS SEHIR,'2015-06-09' AS TARIH,70 TUTAR
- UNION
- SELECT 'ANKARA' AS SEHIR,'2015-02-14' AS TARIH,70 TUTAR
- UNION
- SELECT 'İSTANBUL' AS SEHIR,'2015-03-05' AS TARIH,70 TUTAR
Oluşturulan tablo aşağıdaki gibidir.
- SELECT * FROM #TABLO
| SEHIR | TARIH | TUTAR |
| ANKARA | 22.01.2015 | 40 |
| ANKARA | 24.01.2015 | 70 |
| ANKARA | 10.02.2015 | 10 |
| ANKARA | 14.02.2015 | 70 |
| ANKARA | 04.04.2015 | 10 |
| ANKARA | 28.06.2015 | 20 |
| İSTANBUL | 30.01.2015 | 70 |
| İSTANBUL | 05.03.2015 | 70 |
| İSTANBUL | 15.03.2015 | 30 |
| İSTANBUL | 09.05.2015 | 40 |
| İZMİR | 17.03.2015 | 50 |
| İZMİR | 18.04.2015 | 20 |
| İZMİR | 09.06.2015 | 70 |
| İZMİR | 11.07.2015 | 50 |
Şehirlerin aylara göre dağılımını yapan çapraz sorgu
- SELECT A1.*,TOPLAM FROM
- (SELECT SEHIR,ISNULL([1],0) AS Ocak,ISNULL([2],0) AS Subat,ISNULL([3],0) AS Mart,ISNULL([4],0) AS Nisan,
- ISNULL([5],0) AS Mayis,ISNULL([6],0) AS Haziran,ISNULL([7],0) AS Temmuz,ISNULL([8],0) AS Agustos,
- ISNULL([9],0) AS Eylul,ISNULL([10],0) AS Ekim,ISNULL([11],0) AS Kasim,ISNULL([12],0) AS Aralik
- FROM
- (SELECT SEHIR,MONTH(TARIH) AS AY,SUM(TUTAR) AS TUTAR FROM #TABLO GROUP BY SEHIR,MONTH(TARIH)) AS DATA
- PIVOT (SUM(TUTAR) FOR AY IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PIVOTTABLE) AS A1
- LEFT OUTER JOIN
- (SELECT SEHIR,SUM(TUTAR) AS TOPLAM FROM #TABLO GROUP BY SEHIR) AS A2
- ON A1.SEHIR=A2.SEHIR
- UNION
- SELECT 'TOPLAM' AS TOPLAM, SUM(A1.Ocak), SUM(A1.Subat), SUM(A1.Mart), SUM(A1.Nisan), SUM(A1.Mayis), SUM(A1.Haziran),
- SUM(A1.Temmuz), SUM(A1.Agustos), SUM(A1.Eylul), SUM(A1.Ekim), SUM(A1.Kasim), SUM(A1.Aralik),
- (SELECT SUM(TUTAR) AS TOPLAM FROM #TABLO) AS TOPLAM
- FROM
- (SELECT SEHIR,ISNULL([1],0) AS Ocak,ISNULL([2],0) AS Subat,ISNULL([3],0) AS Mart,ISNULL([4],0) AS Nisan,
- ISNULL([5],0) AS Mayis,ISNULL([6],0) AS Haziran,ISNULL([7],0) AS Temmuz,ISNULL([8],0) AS Agustos,
- ISNULL([9],0) AS Eylul,ISNULL([10],0) AS Ekim,ISNULL([11],0) AS Kasim,ISNULL([12],0) AS Aralik
- FROM
- (SELECT SEHIR,MONTH(TARIH) AS AY,SUM(TUTAR) AS TUTAR FROM #TABLO GROUP BY SEHIR,MONTH(TARIH)) AS DATA
- PIVOT (SUM(TUTAR) FOR AY IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PIVOTTABLE) AS A
| SEHIR | Ocak | Subat | Mart | Nisan | Mayis | Haziran | Temmuz | Agustos | Eylul | Ekim | Kasim | Aralik | TOPLAM |
| ANKARA | 110 | 80 | 0 | 10 | 0 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 220 |
| İSTANBUL | 70 | 0 | 100 | 0 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 210 |
| İZMİR | 0 | 0 | 50 | 20 | 0 | 70 | 50 | 0 | 0 | 0 | 0 | 0 | 190 |
| TOPLAM | 180 | 80 | 150 | 30 | 40 | 90 | 50 | 0 | 0 | 0 | 0 | 0 | 620 |
Not: MS SQL Server compatibility düzeyi SQL Server 2005 için 80, SQL Server 2008 için 100, SQL Server 2012 için 110, SQL Server 2014 için 120 olmalıdır.