Pivot - dynamiczna ilość kolumn.

Witam,
Poproszę Was o pomoc, ponieważ utknąłem :-(.
Serwer SQL 2012.
Zrobiłem "klasycznego" Pivota..., działa bez problemu, ale nie jest dynamiczny (ilość i opisy kolumn).

Select *
FROM
-- Wyrażenie z danymi.
(
SELECT DISTINCT
dbo.MULG_SL_ZAWARTOSC.SLZW_TRESC AS GR_ZAM,
MULG_SL_ZAWARTOSC1.SLZW_TRESC AS GR_TECHN,
VRP_SPRZ_KOR_MG_SUMA_WZ.SPRZ_DATA_SPRZ AS DATA_SP,
TOWARY1.TWR_NUMER AS GR_NUMER,
TOWARY1.TWR_NAZWA AS GR_NAZWA,
TOWARY.TWR_NUMER AS NUMER,
TOWARY.TWR_NAZWA AS NAZWA,
SUM(VRP_SPRZ_KOR_MG_SUMA_WZ.VRPILWAR_ILOSC_KG) AS KG
FROM
VRP_SPRZ_KOR_MG_SUMA_WZ
INNER JOIN TOWARY ON (VRP_SPRZ_KOR_MG_SUMA_WZ.WZN_TWR_ID = TOWARY.TWR_ID)
LEFT OUTER JOIN dbo.TWR_GRUPY ON (TOWARY.TWR_ID = dbo.TWR_GRUPY.TWRGR_TWR_ID)
LEFT OUTER JOIN dbo.MULG_SL_ZAWARTOSC ON (dbo.TWR_GRUPY.TWRGR_SLZW_ID = dbo.MULG_SL_ZAWARTOSC.SLZW_ID)
LEFT OUTER JOIN dbo.MULG_SLOWNIKI ON (dbo.MULG_SL_ZAWARTOSC.SLZW_SL_ID = dbo.MULG_SLOWNIKI.SL_ID)
INNER JOIN dbo.MULG_SL_ZAWARTOSC MULG_SL_ZAWARTOSC1 ON (TOWARY.TWR_ID_TECH = MULG_SL_ZAWARTOSC1.SLZW_ID)
INNER JOIN dbo.TOWARY TOWARY1 ON (TOWARY.TWR_WYROB_ID = TOWARY1.TWR_ID)
WHERE
SPRZ_FLA_ID = 1 AND
WZN_USLUGA = 'N' AND
(dbo.TWR_GRUPY.TWRGR_SL_ID IS NULL OR
dbo.TWR_GRUPY.TWRGR_SL_ID = 265) AND
-- Ograniczenie aby nie "orać" serwera, na testach.
SPRZ_DATA_SPRZ >= '2017-01-22' AND
SPRZ_DATA_SPRZ <= '2017-01-24'
AND TOWARY1.TWR_NUMER IN (92700,70160)
GROUP BY
dbo.MULG_SL_ZAWARTOSC.SLZW_TRESC,
MULG_SL_ZAWARTOSC1.SLZW_TRESC,
TOWARY.TWR_NUMER,
TOWARY.TWR_NAZWA,
TOWARY1.TWR_NUMER,
TOWARY1.TWR_NAZWA,
VRP_SPRZ_KOR_MG_SUMA_WZ.SPRZ_DATA_SPRZ
HAVING
SUM(VRP_SPRZ_KOR_MG_SUMA_WZ.VRPILWAR_WARTOSC_NETTO) <> 0
) AS TEMP_PVT

-- Tworzymy PIVOT.
PIVOT
(SUM(KG) FOR DATA_SP IN ([2017.01.23],[2017.01.24])
) AS PVT

Ale chciałbym aby opisy kolumn "dobierały się" automatycznie.
I mam z tym problem...
Nazwy kolumn to podzapytanie (Z dużą ilością powtórzeń dlatego Distinct).
Select DISTINCT
Convert(Date, SPRZ_DATA_SPRZ)
-- Zmiana daty na yyyy-mm-dd aby pasowała do opisów kolumn zwracanych w zapytaniu głównym.
FROM
VRP_SPRZ_KOR_MG_SUMA_WZ
WHERE
VRP_SPRZ_KOR_MG_SUMA_WZ.SPRZ_FLA_ID = 1 AND
VRP_SPRZ_KOR_MG_SUMA_WZ.WZN_USLUGA = 'N' AND
VRP_SPRZ_KOR_MG_SUMA_WZ.SPRZ_DATA_SPRZ >= '2017-01-21' AND
VRP_SPRZ_KOR_MG_SUMA_WZ.SPRZ_DATA_SPRZ <= '2017-01-24')

Mogę poprosić bardziej doświadczonych użytkowników o pomoc i wytłumaczenie jak to połączyć?
Próbowałem z samouczków z internetu ale cały czas mam błąd.

• przy zmiennej, że dużo powtórzeń
• lub z wczytaniem do ([2017.01.23],[2017.01.24]) nie rozpoznaje tego co piszę.

Chciałbym zrozumieć w czym tkwi problem.
Klasyczny piwot to mniej więcej taka struktura zapytania, i to mi działa.

Select *
FROM
-- Wyrażenie z danymi.
(
"Klasyczny" Select
) AS TEMP_PVT
-- Tworzymy PIVOT.
PIVOT
(SUM(KG) FOR DATA_SP IN ([2017.01.23],[2017.01.24])
"Pole do danych" FOR "Nazwa kolumny dla opisów kolumn" IN ("Opisy kolumn")
) AS PVT

Dzięki,
Od jutra zaczynam "walkę".
Dzięki, przyznam się, że bardzo zmylił mnie "wyremowany" kod.

Idzie z oporami.... Mam już zmienne, do kolumn. I działa to całkiem nieźle....

DECLARE @ColumnName AS NVARCHAR(MAX);

SELECT @ColumnName = ISNULL (@ColumnName + ',','') + QUOTENAME (KLUCZ)
FROM (
--Lista kluczy do kolumn.
SELECT DISTINCT
CONVERT(VARCHAR(4),DATEPART (YYYY, VRP_SPRZ_KOR_MG_SUMA_WZ.SPRZ_DATA_SPRZ)) + '-' + CONVERT(VARCHAR(1),DATEPART (QQ, VRP_SPRZ_KOR_MG_SUMA_WZ.SPRZ_DATA_SPRZ)) AS KLUCZ
FROM
VRP_SPRZ_KOR_MG_SUMA_WZ
WHERE
SPRZ_FLA_ID = 1 AND
WZN_USLUGA = 'N' AND
SPRZ_DATA_SPRZ >= '2017-01-01' AND
SPRZ_DATA_SPRZ <= '2018-01-24'
)AS COLUMN_TEMP;

-- Wyświetlenie wyników.
SELECT @ColumnName

WYNIK: Lista kolumn. Więc idzie w dobra stronę…
[2017-3],[2017-2],[2017-4],[2017-1],[2018-1]

Ale mam problem jak to przesortować…, aby uzyskać.
[2017-1], [2017-2], [2017-3], [2017-4], [2017-1]
Zawsze ma się to sortować ROK-KWARTAL, taki klucz mogę wygenerować dla danych dla kolumn do PIVOT.
Gdy próbuję ORDER BY – mam informację że sortowanie w SubQuery jest niedozwolone.

Przy okazji będzie ktoś tak miły i wytłumaczy mi ten zapis...?
SELECT @ColumnName = ISNULL (@ColumnName + ',','') + QUOTENAME (KLUCZ)
Robi to co powinien, ale chciałbym zrozumieć krok po kroku jak odczytać poszczególne działania...

SELECT @ColumnName = ISNULL (@ColumnName + ',','') + QUOTENAME (KLUCZ)
FROM (
--Lista kluczy do kolumn.
  SELECT DISTINCT
    CONVERT(VARCHAR(4),DATEPART (YYYY, SPRZ_DATA_SPRZ)) + '-' + CONVERT(VARCHAR(1),DATEPART (QQ, SPRZ_DATA_SPRZ)) AS KLUCZ
  FROM
    VRP_SPRZ_KOR_MG_SUMA_WZ
  WHERE
    SPRZ_FLA_ID = 1 AND
    WZN_USLUGA = 'N' AND
    SPRZ_DATA_SPRZ >= '2017-01-01' AND
    SPRZ_DATA_SPRZ <= '2018-01-24'
)  AS COLUMN_TEMP
ORDER BY klucz;

powinno zadziałać. A to ISNULL (@ColumnName + ',','') działa tak, że ISNULL zwraca pierwszy nie pusty (NULL) element. Przy "pierwszym przebiegu" @ColumnName jest NULLem więc @ColumnName + ',' też jest NULLem więc ISNULL zwraca '' (czyli drugi element), dodaje do niego + QUOTENAME (KLUCZ) i przypisuje go do @ColumnName. Przy kolejnym przebiegu @ColumnName ma wartość i nie jest już NULLem więc ISNULL zwraca @ColumnName + ','.
Inaczej przy pierwszym rekordzie ta linijka robi tak
@ColumnName = '' + QUOTENAME (KLUCZ)
a przy kolejnych tak
@ColumnName = @ColumnName + ',' + QUOTENAME (KLUCZ)

W końcu się udało znaleźć czas aby skończyć Pivota, który działa :-)
Poproszę jednak Was o pomoc w posortowaniu kolumn nagłówków.
Dane muszę mieć pogrupowane "LATAMI" i "TYGODNIAMI" - dane niezbędne do tego to pobieram z "daty".
I łączę w ciąg znaków: YYYY/WW, który daje mi nagłówki kolumn jednak mam problem z zachowaniem prawidłowego układu:
2017/52
2017/53
2018/1
2018/2
(...)
2018/10
2018/11
itd... może być TYDZIEŃ/ROK

Ale Kolumny układają mi się:
2017/52
2017/53
2018/1
2018/10
2018/11
2018/2
(...)
Jak mogę wprowadzić sortowanie nazw kolumn do zapytania?
Każda próba posortowania danych w podzapytaniu wyrzuca błąd.

SET DATEFIRST 1;
DECLARE @ColumnName AS NVARCHAR(MAX);
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);

DECLARE @dp datetime;
DECLARE @dk datetime;

SET @dp = '2018-01-01';
SET @dk = '2018-03-18';

-- Tworzymy tabelę tymczasową z danymi.
(SELECT DISTINCT
VRP_ROZBIOR_ROZLICZENIE_WE_WY.VRP_RODZAJ AS TYP,
TOWARY.TWR_NUMER AS INDEKS,
TOWARY.TWR_NAZWA AS NAZWA,
CONVERT(VARCHAR(4),DATEPART(YYYY, ZLECENIA.ZLC_DATA)) +'/'+ CONVERT(VARCHAR(2),DATEPART(WW, ZLECENIA.ZLC_DATA)) AS OKRES,
(VRP_ROZBIOR_ROZLICZENIE_WE_WY.VRP_ILOSC_ROZLICZ_WE + VRP_ROZBIOR_ROZLICZENIE_WE_WY.VRP_ILOSC_ROZLICZ_WY) AS ILOSCI
INTO #TEMP_PVT
FROM
VRP_ROZBIOR_ROZLICZENIE_WE_WY VRP_ROZBIOR_ROZLICZENIE_WE_WY
INNER JOIN dbo.ZLECENIA ZLECENIA ON (VRP_ROZBIOR_ROZLICZENIE_WE_WY.VRP_ZLC_ID = ZLECENIA.ZLC_ID)
AND (VRP_ROZBIOR_ROZLICZENIE_WE_WY.VRP_ZLC_ID = ZLECENIA.ZLC_ID)
INNER JOIN grot.dbo.TOWARY TOWARY ON (VRP_ROZBIOR_ROZLICZENIE_WE_WY.VRP_TWR_ID = TOWARY.TWR_ID)
WHERE
ZLECENIA.ZLC_DATA BETWEEN @dp AND @dk AND
ZLECENIA.ZLC_NAZWA IN ('Rozbiór WP') AND
ZLECENIA.ZLC_STATUS <> 'A'
);

-- Tworzymy Listę kolumn PIVOT.
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME (OKRES)
-- Lista nazw kolumn, zapytanie SELECT.
FROM (SELECT DISTINCT
CONVERT(VARCHAR(4),DATEPART(YYYY, ZLECENIA.ZLC_DATA)) +'/'+ CONVERT(VARCHAR(2),DATEPART(WW, ZLECENIA.ZLC_DATA)) AS OKRES
FROM
ZLECENIA
WHERE
ZLECENIA.ZLC_DATA BETWEEN @dp AND @dk AND
ZLECENIA.ZLC_NAZWA IN ('Rozbiór WP') AND
ZLECENIA.ZLC_STATUS <> 'A') AS COLUMNNAMELIST

-- Tworzymy dynamiczny PIVOT.
SET @DynamicPivotQuery =
'SELECT *
FROM #TEMP_PVT
PIVOT(SUM(ILOSCI) FOR OKRES IN (' + @ColumnName + ')) AS PVTTABLE'

-- Uruchamianie Pivot.
EXEC sp_executesql @DynamicPivotQuery

DROP TABLE #TEMP_PVT

Cały myk polega na tym, ze do łączenia stringów używasz tzw. metody małpokwej i z racji tego nie masz wpływu na to w jakiej kolejności te dane się skleją. Jak powszechnie wiadomo w relacyjnych bazach nie ma czegoś takiego jak kolejność rekordów.

Można jednak wykorzystać właściwość MS SQL, ze akurat w 99,9 % przypadkow kolejność będzie zgodna z kluczem klastrującym, zatem obadaj ten przykład

--dane wejściowe
CREATE TABLE tab
    (okres varchar(7))
;
INSERT INTO tab
    (okres)
VALUES
    ('2017/01'),
    ('2018/01'),
    ('2018/03'),
    ('2017/02'),
    ('2017/12'),
    ('2017/11')
go

declare @colnames varchar(2000)

select * into #tmp
from tab;


set @colnames = ''
select @colnames = @colnames + ',' + QUOTENAME(OKRES) from #tmp
select @colnames as [Kolumny nieposrtowane]

ALTER TABLE #tmp ALTER COLUMN okres varchar(7) not null
ALTER TABLE #tmp ADD PRIMARY KEY CLUSTERED (okres) 


set @colnames = ''
select @colnames = @colnames + ',' + QUOTENAME(OKRES) from #tmp
select @colnames as [Kolumny posrtowane]


drop table #tmp
drop table tab

Próbuję...
Ale (...) nie sortuje.
,[2017/49],[2017/50],[2017/51],[2017/52],[2017/53],[2018/1],[2018/10],[2018/11],[2018/2],[2018/3],[2018/4],[2018/5],[2018/6],[2018/7],[2018/8],[2018/9]

DECLARE @dp datetime;
DECLARE @dk datetime;

SET @dp = '2017-12-01';
SET @dk = '2018-03-18';

DECLARE @colnames VARCHAR(2000);

SELECT DISTINCT
CONVERT(varchar(4),DATEPART(yyyy, ZLECENIA.ZLC_DATA)) +'/'+ CONVERT(varchar(2),DATEPART(ww, ZLECENIA.ZLC_DATA)) AS OKRES
INTO #LISTA_NIPOSORTOWANA
FROM
ZLECENIA
WHERE
ZLECENIA.ZLC_DATA BETWEEN @dp AND @dk AND
ZLECENIA.ZLC_NAZWA IN ('Rozbiór WP') AND
ZLECENIA.ZLC_STATUS <> 'A'

ALTER TABLE #LISTA_NIPOSORTOWANA ALTER COLUMN OKRES VARCHAR(7) NOT NULL
ALTER TABLE #LISTA_NIPOSORTOWANA ADD PRIMARY KEY CLUSTERED (OKRES)

SET @colnames = ''
SELECT @colnames = @colnames + ',' + QUOTENAME(OKRES) FROM #LISTA_NIPOSORTOWANA
SELECT @colnames AS [Kolumny posrtowane]

DROP TABLE #LISTA_NIPOSORTOWANA

Sortuje dobrze. To jest ciąg znaków dlatego kolejność to 1,10,11,2,22 itd. zwróć uwagę że ja w swoim przykładzie dopełniam jednocyfrowe tygodnie 0

Haha,
Czyli aby to zrobić muszę policzyć długość ciągu dla "WW"
i dla "1" dodać z przodu zero.
dla "2" przepisać.
I wtedy skleić w ciąg docelowy?
Wystarczy CASE (...) wbudowany w ciąg ?

Nie, wystarczy użyć funkcji RIGHT przecież masz dwie możliwości 1 lub 2 znaki:

CONVERT(varchar(4),DATEPART(yyyy, ZLECENIA.ZLC_DATA)) +'/'+ RIGHT('00' + CONVERT(varchar(2),DATEPART(ww, ZLECENIA.ZLC_DATA)),2)

Możesz też zmienić metodę sklejania stringów która, umożliwi kontrolę kolejności:

DECLARE @CN1 VARCHAR(2000)
SET @CN1 = (SELECT  ',' + QUOTENAME(CONVERT(varchar(4),DATEPART(yyyy, ZLECENIA.ZLC_DATA)) +'/'+ CONVERT(varchar(2),DATEPART(ww, ZLECENIA.ZLC_DATA)))
           FROM ZLECENIA
         ORDER BY YEAR(ZLC_DATA),DATEPART(ww, ZLECENIA.ZLC_DATA)
            FOR XML PATH(''))
            
SELECT @CN1            

Warto też pamiętać, że w przypadku PIVOTA w T-SQL możesz niezależnie genrować kolumny i wykorzystać to że podane wartości nie muszą występować. jeżeli w przykładzie mamy daty z zakresu: 2017-11-02 - 2018-03-21

To nic nie stoi na przeszkodzie, aby wygenerować kolumny niezależnie od wartości:

DECLARE @minDT datetime
        ,@maxDT datetime
        ,@CN3 VARCHAR(2000)

select 
    @minDT = min(ZLC_DATA)
    ,@maxDT=max(ZLC_DATA) 
from 
     ZLECENIA
SET @CN3 = (
SELECT
   ',' + QUOTENAME(CONVERT(varchar(4),DATEPART(yyyy, DTW)) +'/'+ CONVERT(varchar(2),DATEPART(ww, DTW)))
FROM
  (SELECT 
      DATEADD(DAY,-(DATEPART(DW,DATEADD(WEEK, x.number, @minDT))-2),DATEADD(WEEK, x.number, @minDT)) as [dtw]
      ,x.number
   FROM 
       master.dbo.spt_values x
   WHERE 
       x.type = 'P' 
       AND x.number <= DATEDIFF(WEEK, @minDT, DATEADD(WEEK,0,CAST(@maxDT AS DATE)))
   ) AS O
   ORDER BY 
       number
   FOR XML PATH(''))
   
  SELECT @CN3;

I zrobić zapytanie:

 select
    klient,[2017/44],[2017/45],[2017/46],[2017/47],[2017/48],[2017/49],[2017/50],[2017/51],[2017/52],[2018/1],[2018/2],[2018/3],[2018/4],[2018/5],[2018/6],[2018/7],[2018/8],[2018/9],[2018/10],[2018/11],[2018/12]
 FROM
     (SELECT
           klient
           ,CONVERT(varchar(4),DATEPART(yyyy, ZLECENIA.ZLC_DATA)) +'/'+ CONVERT(varchar(2),DATEPART(ww, ZLECENIA.ZLC_DATA)) okres
           ,kwota
      from
           ZLECENIA) AS DT
 PIVOT (SUM(KWOTA)
        FOR OKRES IN([2017/44],[2017/45],[2017/46],[2017/47],[2017/48],[2017/49],[2017/50],[2017/51],[2017/52],[2018/1],[2018/2],[2018/3],[2018/4],[2018/5],[2018/6],[2018/7],[2018/8],[2018/9],[2018/10],[2018/11],[2018/12])
        ) AS PVT;

Przykłady: http://sqlfiddle.com/#!18/146b5/18

Dzięki,
Zastosowałem:
CONVERT(VARCHAR(4),DATEPART(yyyy, ZLECENIA.ZLC_DATA)) +'/'+ RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(ww, ZLECENIA.ZLC_DATA)),2)
co dało oczekiwany wynik.
Czas generowanie danych dla 14 msc: 3,032sek.

Podsumowując moje zmagania z dynamicznym pivot-em podsumowałbym to tak:
Można to ująć w kilka zrozumiałych kroków (może się komuś przydadzą).

SET DATEFIRST 1;
DECLARE @ColumnName AS NVARCHAR(MAX);
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);

DECLARE @dp datetime; -- Moje dane do filtrowania rekordów.
DECLARE @dk datetime;
SET @dp = '2017-01-01';
SET @dk = '2018-03-18';

-- Tworzymy tabelę tymczasową z danymi (Okres - nazwy kolumn, ILOSCI - dane do wstawienia)
(SELECT
...
"Nazwykolumn" As OKRES
"Dane" As ILOSCI
INTO #TEMP_PVT
...
);

-- Tworzymy tabelę tymczasową z nazwami dla kolumn pod sortowanie nazw. (OKRES - nazwy kolumn).
(SELECT
"Nazwakolumn" As OKRES
INTO #LISTA_POSORTOWANA
);

-- Moje dodatkowe sortowanie (nie zawsze konieczne, zależy od nazw kolumn).
ALTER TABLE #LISTA_POSORTOWANA ALTER COLUMN OKRES VARCHAR(7) NOT NULL
ALTER TABLE #LISTA_POSORTOWANA ADD PRIMARY KEY CLUSTERED (OKRES)

-- Tworzymy Listę kolumn PIVOT tutaj wykorzystujemy wcześniej przygotowane dane. Uwaga na zgodność nazw pól i tabel.
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME (OKRES)
-- Lista nazw kolumn, zapytanie SELECT.
FROM (SELECT OKRES FROM #LISTA_POSORTOWANA) AS COLUMNNAMELIST

-- Tworzymy dynamiczny PIVOT tutaj wykorzystujemy wcześniej przygotowane dane. Uwaga na zgodność nazw pól i tabel.
SET @DynamicPivotQuery =
'SELECT *
FROM #TEMP_PVT
PIVOT(SUM(ILOSCI) FOR OKRES IN (' + @ColumnName + ')) AS PVTTABLE'

-- Uruchomienie PIVOT.
EXEC sp_executesql @DynamicPivotQuery

DROP TABLE #TEMP_PVT
DROP TABLE #LISTA_PVT
DROP TABLE #LISTA_POSORTOWANA