SQL - Double pivot - Second pivot (With Count) not working as expected

Question

I have a table like the following. Let's call it TBL1:

                    SINIF                                        NAME_  CURCODE    TARIH       TARIHS     TAKSIT
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2015    2015 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2016    2016 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2016    2016 SAYI   28770,13
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD     2016    2016 SAYI   28732,89
     02 - YATIRIM KREDİSİ         TEB KREDİ - 100.000 EUR (ARAÇ KREDİ)      EUR     2015    2015 SAYI    2024,67
     02 - YATIRIM KREDİSİ         TEB KREDİ - 100.000 EUR (ARAÇ KREDİ)      EUR     2015    2015 SAYI    2024,67
     02 - YATIRIM KREDİSİ         TEB KREDİ - 100.000 EUR (ARAÇ KREDİ)      EUR     2015    2015 SAYI    2024,67

Note that TARIHS is just version of TARIH with a string SAYI added at the end. I use it because I need different column names for a double pivot. I want to construct a double pivot that does the following:

SUM(TAKSIT) vs TARIH (2015, 2016, 2017)
COUNT(TARIHS) vs TARIHS (2015 SAYI, 2016 SAYI, 2017 SAYI)

Hence, I would like to get a table like the following:

                    SINIF                                        NAME_  CURCODE        2015      2016    2017    2015 SAYI    2016 SAYI    2017 SAYI
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD    345243.6   86310.9    NULL           12            3         NULL
     02 - YATIRIM KREDİSİ         TEB KREDİ - 100.000 EUR (ARAÇ KREDİ)      EUR     6074.01      NULL    NULL            3         NULL         NULL

However, when I construct the double pivot, I get the following:

                    SINIF                                        NAME_  CURCODE        2015      2016    2017    2015 SAYI    2016 SAYI    2017 SAYI
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD    345243.6      NULL    NULL            1            0            0
01 - TİCARİ (GENEL) KREDİ   VAKIFBANK KREDİ - 1.500.000 USD (TÜKETİCİ)      USD        NULL   86310.9    NULL            0            1            0
     02 - YATIRIM KREDİSİ         TEB KREDİ - 100.000 EUR (ARAÇ KREDİ)      EUR     6074.01      NULL    NULL            1            0            0

Can someone help me with my code? I did not paste my original code here because it is a bit long, but I summarized it for you to get a general idea.

I also would like to let you know that my code works if I remove TARIHS from all the selections and just want to do the first pivot. However, When I add the second pivot with TARIHS into the mix, I get a table that is not what I want.

THANKS!

Answer 1

The problem might be you are selecting the all the column SINIF, NAME_ , CURCODE, TARIH, TARIHS, TAKSIT in pivot source query. Try changing your query like this.

in the source pivot query

SELECT a.SINIF,a.NAME_,a.CURCODE,
       [2015],[2016],[2017],
       [2015 SAYI],[2016 SAYI],[2017 SAYI]
FROM  (SELECT *
       FROM   (SELECT SINIF,NAME_,CURCODE,TAKSIT,tarih
               FROM   Tablename) a
              PIVOT (Sum(TAKSIT)
                    FOR tarih IN([2015],[2016],[2017]))piv) a
      JOIN(SELECT *
           FROM   (SELECT SINIF,NAME_,CURCODE,TARIHS
                   FROM   Tablename) a
                  PIVOT (Count(TARIHS)
                        FOR TARIHS IN([2015 SAYI],[2016 SAYI],[2017 SAYI]))piv) b
        ON a.SINIF = b.SINIF
           AND a.NAME_ = b.NAME_
           AND a.CURCODE = b.CURCODE 

SQLFIDDLE DEMO