I have migrated my MS Access database to SQL Server. I have a pivot query, which I am unable to adapt.
In MS Access, it is:
TRANSFORM FIRST(eff.efi) AS efici
SELECT eff.semana_ano, FIRST(eff.data), LAST(eff.data), ROUND(Avg(eff.efi), 2) AS media_semana
FROM
(SELECT dia_semana, semana_ano, efi
FROM tmp_lista) AS eff
GROUP BY eff.semana_ano
PIVOT eff.ndia_semana
The data in SQL Server is:
CREATE TABLE tmp_lista
(
dia date,
dia_semana varchar(5),
semana_ano int,
efi decimal(5,2)
);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-01', 'D5', 5, 0.98);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-02', 'D6', 5, 0.5);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-03', 'D7', 5, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-04', 'D1', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-05', 'D2', 6, 0.49);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-06', 'D3', 6, 0.65);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-07', 'D4', 6, 0.3);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-08', 'D5', 6, 1.18);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-09', 'D6', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-10', 'D7', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-11', 'D1', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-12', 'D2', 7, 0.57);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-13', 'D3', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-14', 'D4', 7, 0.51);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-15', 'D5', 7, 0.65);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-16', 'D6', 7, 0.49);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-17', 'D7', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-18', 'D1', 8, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-19', 'D2', 8, 0.34);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-20', 'D3', 8, 0.46);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-21', 'D4', 8, 0.43);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-22', 'D5', 8, 0.32);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-23', 'D6', 8, 0.62);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-24', 'D7', 8, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-25', 'D1', 9, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-26', 'D2', 9, 0.62);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-27', 'D3', 9, 0.44);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-28', 'D4', 9, 0.54);
dia |dia_semana|semana_ano|efi |
----------|----------|----------|------|
2024-02-01|D5 | 5| 0.98|
2024-02-02|D6 | 5| 0.50|
2024-02-03|D7 | 5|[NULL]|
2024-02-04|D1 | 6|[NULL]|
2024-02-05|D2 | 6| 0.49|
2024-02-06|D3 | 6| 0.65|
2024-02-07|D4 | 6| 0.30|
2024-02-08|D5 | 6| 1.18|
2024-02-09|D6 | 6|[NULL]|
2024-02-10|D7 | 6|[NULL]|
2024-02-11|D1 | 7|[NULL]|
2024-02-12|D2 | 7| 0.57|
2024-02-13|D3 | 7|[NULL]|
2024-02-14|D4 | 7| 0.51|
2024-02-15|D5 | 7| 0.65|
2024-02-16|D6 | 7| 0.49|
2024-02-17|D7 | 7|[NULL]|
2024-02-18|D1 | 8|[NULL]|
2024-02-19|D2 | 8| 0.34|
2024-02-20|D3 | 8| 0.46|
2024-02-21|D4 | 8| 0.43|
2024-02-22|D5 | 8| 0.32|
2024-02-23|D6 | 8| 0.62|
2024-02-24|D7 | 8|[NULL]|
2024-02-25|D1 | 9|[NULL]|
2024-02-26|D2 | 9| 0.62|
2024-02-27|D3 | 9| 0.44|
2024-02-28|D4 | 9| 0.54|
I managed to adapt to this point:
SELECT
semana_ano, D1, D2, D3, D4, D5, D6, D7
FROM
(SELECT dia_semana, semana_ano, efi FROM tmp_lista) AS dp
PIVOT
(SUM(efi) FOR dia_semana IN (D1, D2, D3, D4, D5, D6, D7)) result_dp;
semana_ano|D1 |D2 |D3 |D4 |D5 |D6 |D7 |
----------|------|------|------|------|------|------|------|
5 |[NULL]|[NULL]|[NULL]|[NULL]| 0.98| 0.50|[NULL]|
6 |[NULL]| 0.49| 0.65| 0.30| 1.18|[NULL]|[NULL]|
7 |[NULL]| 0.57|[NULL]| 0.51| 0.65| 0.49|[NULL]|
8 |[NULL]| 0.34| 0.46| 0.43| 0.32| 0.62|[NULL]|
9 |[NULL]| 0.62| 0.44| 0.54|[NULL]|[NULL]|[NULL]|
You would also need a column for the minimum day of the week, a column with the maximum day of the week, and a column with the average of the 7 days.
Something like:
semana_ano|D1 |D2 |D3 |D4 |D5 |D6 |D7 |min(dia) |max(dia) |avg(7 dias)|
----------|------|------|------|------|------|------|------|----------|----------|-----------|
5 |[NULL]|[NULL]|[NULL]|[NULL]| 0.98| 0.50|[NULL]|2024-02-01|2024-02-03| 0.74|
6 |[NULL]| 0.49| 0.65| 0.30| 1.18|[NULL]|[NULL]|2024-02-04|2024-02-10| 0.66|
7 |[NULL]| 0.57|[NULL]| 0.51| 0.65| 0.49|[NULL]|2024-02-11|2024-02-17| 0.56|
8 |[NULL]| 0.34| 0.46| 0.43| 0.32| 0.62|[NULL]|2024-02-18|2024-02-24| 0.43|
9 |[NULL]| 0.62| 0.44| 0.54|[NULL]|[NULL]|[NULL]|2024-02-25|2024-02-28| 0.53|
PIVOT is not flexible enough to do multiple types of aggregation. As @siggemannen suggested, conditional aggregation is the ticket. E.g.:
SELECT semana_ano,
D1 = NULLIF(SUM(CASE dia_semana WHEN 'D1' THEN efi ELSE 0 END),0),
D2 = NULLIF(SUM(CASE dia_semana WHEN 'D2' THEN efi ELSE 0 END),0),
D3 = NULLIF(SUM(CASE dia_semana WHEN 'D3' THEN efi ELSE 0 END),0),
D4 = NULLIF(SUM(CASE dia_semana WHEN 'D4' THEN efi ELSE 0 END),0),
D5 = NULLIF(SUM(CASE dia_semana WHEN 'D5' THEN efi ELSE 0 END),0),
D6 = NULLIF(SUM(CASE dia_semana WHEN 'D6' THEN efi ELSE 0 END),0),
D7 = NULLIF(SUM(CASE dia_semana WHEN 'D7' THEN efi ELSE 0 END),0),
[min(dia)] = MIN(dia),
[max(dia)] = MAX(dia),
[avg(7 dias)] = AVG(efi)
FROM tmp_lista
GROUP BY semana_ano;