I want to add a dayname before the Date '01.08.2022, 02.08.2022 etc.' My table looks like this:

I want it to look like this:

My Code is :

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';



SELECT @columns = STUFF((SELECT ','    QUOTENAME(Datum) as Datum from

            (SELECT DISTINCT CONVERT(varchar,Datum,104) as Datum
            FROM  TABLE1
            Where Datum > DATEADD(day, -1, GETDATE())
            and Datum < DATEADD(day,  13, GETDATE())

            )sub

            ORDER BY Datum

        
            
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



SET @sql ='
SELECT * FROM   
(
    select DISTINCT Vorname, Nachname, Bezeichnung, Datum from TABLE1 a
left join TABLE2 b
    ON a.Mitarbeiter_ID = b.Mitarbeiter_ID

    WHERE a.Bezeichnung is not null
    and a.Bezeichnung <> ''''

    and Datum > DATEADD(day, -1, GETDATE())
    and Datum < DATEADD(day,  13, GETDATE())
    
) t 

PIVOT(
    MAX(Bezeichnung)
    FOR Datum IN ('  @columns  ')

) AS pivot_table;';


EXECUTE sp_executesql @sql;

Any suggestions how to combine this date format with dayname?

CodePudding user response：

You can just add an alias to the column names. It's easier to do this if you do a manual pivot using MAX(CASE WHEN, because then you only need one @columns variable, whereas with PIVOT you need to split it up by actual values and aliases.

You are using SQL Server 2019, so you can use the newer STRING_AGG which is much easier.

DECLARE 
    @columns NVARCHAR(MAX), 
    @sql     NVARCHAR(MAX);

SELECT @columns = STRING_AGG(
    CONCAT(
      '  MAX(CASE WHEN Datum = ',
      QUOTENAME(CONVERT(varchar(20), Datum, 112), ''''),
      ' THEN Bezeichnung END) AS ',
      QUOTENAME(LEFT(DATENAME(weekday, Datum), 3)   ' '   CONVERT(varchar(20), Datum, 112))
    )
  , ',
' ) WITHIN GROUP (ORDER BY Datum)
FROM (
    SELECT DISTINCT Datum
    FROM  TABLE1
    Where Datum > DATEADD(day, -1, GETDATE())
      and Datum < DATEADD(day,  13, GETDATE())
) sub;

SET @sql ='
SELECT
  t.Vorname,
  t.Nachname, 
'   @columns   '
FROM   
(
    select DISTINCT Vorname, Nachname, Bezeichnung, Datum
    from TABLE1 a
    left join TABLE2 b ON a.Mitarbeiter_ID = b.Mitarbeiter_ID

    WHERE a.Bezeichnung is not null
      and a.Bezeichnung <> ''''

      and Datum > DATEADD(day, -1, GETDATE())
      and Datum < DATEADD(day,  13, GETDATE())
) t
GROUP BY
  t.Vorname,
  t.Nachname;
';

EXECUTE sp_executesql @sql;