Dynamic and static pivot SQL server table

Question

Good morning. I made table

CREATE TABLE zwierze2 (
id_zwierze INT PRIMARY KEY,
imie VARCHAR(15) NOT NULL CHECK(LEN(imie)>3),
rasa VARCHAR(30) NOT NULL,
wiek CHAR(2) NOT NULL,
rodzaj VARCHAR(30) NOT NULL,
);

With ex. inputs:

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(1, 'Alik', 'amstaf', 4, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(2, 'Alika', 'amstaf', 6, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(3, 'Killer', 'jamnik', 4, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(4, 'Thor', 'dachowiec', 4, 'kot');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(5, 'Randi', 'kundel', 4, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(6, 'Hamer', 'amstaf', 8, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(7, 'Bobik', 'jamnik', 4, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(8, 'Deral', 'jamnik', 6, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(9, 'Doge', 'jamnik', 7, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(10, 'Dogeł', 'amstaf', 9, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(11, 'Szak', 'amstaf', 10, 'pies');

INSERT INTO zwierze2(id_zwierze, imie, rasa, wiek, rodzaj)
VALUES(12, 'Pies', 'kundel', 14, 'pies');

In the result I wanted to get table with column 'rasa', row with each 'wiek' and with counted how many amstaf are in exact wiek='10' etc.

I coded:

SELECT 4, 6, 7, 8, 9, 10, 14, rasa FROM zwierze2
PIVOT (
COUNT(rasa) FOR
wiek IN ([4],[6],[7],[8],[9],[10],[14]))
AS ilosc_psow_danej_rasy_w_danym_wieku

And I didn't code it well, it doesn't work. I need also dynamic version of it, but stacked here already.

EDIT Found another breakpoint, now in dynamic. I don't get unique rasa.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
+ QUOTENAME(wiek)
FROM (SELECT DISTINCT wiek from zwierze) as wiek

SET @DynamicPivotQuery=
N'SELECT rasa, ' + @ColumnName +'
FROM zwierze
PIVOT(Count(id_zwierze)
FOR wiek IN (' + @ColumnName + ')) AS PVTTable'

EXEC(@DynamicPivotQuery);

Answer 1

Given that you want a pivot containing rasa as a column, you can't use it as the aggregate function in the pivot.

Also note that SELECT 1,2,3... will select literal values, instead of the pivot columns. Use [] to escape this. You probably want something along the lines of:

SELECT [4], [6], [7], [8], [9], [10], [14], rasa 
FROM
(
  SELECT rasa, wiek, id_zwierze
  FROM zwierze2
 ) x
PIVOT 
(
  COUNT(id_zwierze) FOR
  wiek IN ([4],[6],[7],[8],[9],[10],[14]))
  AS ilosc_psow_danej_rasy_w_danym_wieku

SqlFiddle here

Example of doing this dynamically here