[SQL] Hoe kolommen per datum in range creeëren

[Scheepers.A]

Ik heb een query waarvan de afgelopen 14 dagen in een range als kolommen zichtbaar moeten zijn i.p.v. in rijen.
Ik kom er niet uit. Wie kan mij helpen?
Ik gebruik SQL server.

Dit heb ik al:
SELECT
T.Taak
,R.Paraaf,
R.Datum
FROM [Logboeken].[dbo].[Taken] T
INNER JOIN Lab AS L ON (L.ID = T.Lab)
LEFT JOIN Registratie R ON (R.Taak = T.Taak)
where T.lab =8
AND (R.Datum >= dateadd(day,-7,getdate()) and R.Datum <= dateadd(day,+7,getdate()) or R.Datum IS NULL)

Resultaat:
Taak Paraaf Datum
PCs & Printers uitzetten NULL NULL
Aanvragenformulieren opruimen as 2021-03-29
Aanvragenformulieren opruimen as 2021-03-24

Wat we willen is:
Kolommen: Taak, datum van de afgelopen 14 dagen (1-3,2-3,3-3, etc)
Rijen: Taak, Per datum paraaf als datum staat ingevuld, anders leeg

[Lethalis]

https://www.sqlshack.com/...posing-rows-into-columns/

Voor de rest hou ik (iig conceptueel) een scheiding aan tussen data en de presentatie daarvan.

Kortom: je hoeft niet altijd alles in SQL op te lossen Soms is het makkelijker om in de presentatielaag van een applicatie dit soort transformaties te doen. Vaak kun je dan uitzonderingen ook makkelijker afvangen.

[MKoole]

Helemaal eens met Lethalis. Zo'n verzoek verwerk je in de presentatielaag.

Is dat een optie?

[CurlyMo]

I tripple that

[HansvDr]

Kan prima buiten de presentatie-laag. Ik gebruik Pivot of een cursor hiervoor. Bij mijn data is het sneller vanuit een stored procedure dan alles achteraf in de presentatie-laag. Met als extra voordeel dat een export naar bv excel ook zo gemaakt is.

[Scheepers.A]

In de tussentijd al een stuk verder gekomen met de query. Kolommen per datum tot een aantal dagen terug.
Waar ik nog mee zit is dat de datum per rij komt en niet 1 rij met de datum per kolom
Bijv: Aanvragenformulieren opruimen as 2021-03-29
Aanvragenformulieren opruimen as 2021-03-24

Moet 1 rij worden waarin in de kolommen 24-3 en 29-3 'gevuld' komt te staan en de andere kolommen 'niets' komt te staan.

Zie code:
declare @lastweek date
declare @now date
set @now = getdate()
set @lastweek = dateadd(day,-07,@now)

DECLARE @sql NVARCHAR(MAX) = N'SELECT DISTINCT(T.Taak), R.Paraaf, R.Datum';

;WITH dr AS
(
SELECT MinDate = MIN(@lastweek),
MaxDate = MAX(@now)
FROM dbo.Registratie

),
n AS
(
SELECT TOP (DATEDIFF(DAY, (SELECT MinDate FROM dr), (SELECT MaxDate FROM dr)) + 1)
d =DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1,
(SELECT MinDate FROM dr))
FROM sys.all_objects
)
SELECT @sql += ',
' + QUOTENAME(d) + ' = CASE WHEN ''' + CONVERT(CHAR(10), d, 120)
+ ''' BETWEEN R.Datum AND R.Datum THEN ''gevuld'' ELSE ''niets'' END' FROM n;

SELECT @sql += ' FROM dbo.Taken T'
SELECT @sql += ' LEFT JOIN Registratie R ON (R.Taak = T.Taak) WHERE T.Lab = 2'
SELECT @sql += ' AND (R.Datum >= dateadd(day,-7,getdate()) and R.Datum <= dateadd(day,+7,getdate()) or R.Datum IS NULL)';

[ElCondor]

Pssst, zet je code even tussen

code:

[code][/code]

blokken

[pedorus]

Scheepers.A schreef op woensdag 31 maart 2021 @ 13:39:
In de tussentijd al een stuk verder gekomen met de query.

Je bent verder gekomen, maar waarom wil je zo graag verder het moeras in? Datums zijn typisch gezien rijen, daar moet de ontvanger mee kunnen werken. Datums als kolommen is meestal foute boel

Maar ok, stel we gooien alle dingen overboord, dan heb je meerdere zaken om op te lossen:

• Hoe krijg ik een lijstje datums van start->einddatum?
• Hoe zet ik dat lijstje om in kolomnamen?
• Hoe vul ik de kolommen?

Voor die 3 problemen zijn allerlei oplossingen. Omdat ik het nog wel een grappige puzzel vond, een oplossing. Eerst wat voorbeelddata, dan de code om dat om te zetten naar report op de rijen, en datums als kolommen:

SQL:

create table registrations(report varchar, date date);
insert into registrations values ('A', '2021-01-01');
insert into registrations values ('B', '2021-01-01');
insert into registrations values ('A', '2021-01-10');
insert into registrations values ('C', '2021-01-03');

SQL:

declare @date1 date = '2021-01-01';
declare @date2 date = '2021-01-14';
declare @cols nvarchar(max);

with dateRange as
(
  select dt = @date1
  union all
  select dateadd(dd, 1, dt) from dateRange where dt < @date2
)
select @cols = stuff((select ',' + quotename(dt) from dateRange 
                      for xml path('')), 1, 1, '');

execute('select report, ' + @cols + ' from
(select report, date from registrations) as SourceTable
pivot
(
count(date) for date IN (' + @cols + ')
) as PivotTable order by report;');
GO

Dit lijkt te werken, zie: http://www.sqlfiddle.com/#!18/73521/1/0 . Wellicht kun je het omzetten naar jouw casus.