如果 Value 与日期存在，则获取 1

如果你不是在寻找动态解决方案，你可以使用条件聚合：

select t1.Personalnr, 
       MAX(case when t2.Date = '2023-09-01' then 1 else 0 end) as [2023-09-01],
       MAX(case when t2.Date = '2023-09-02' then 1 else 0 end) as [2023-09-02]
from table1 t1
inner join table2 t2 on t1.Personalnr = t2.Personalnr
group by t1.Personalnr

在这里演示

对于数据透视表来说，这也是一个很好的用例。
我创建了一个包含 2023 年所有日期的Calendar_Table，无论员工在表 2 中是否有特定日期的数据，我都将使用它几乎用作辅助表，以便能够透视我的数据。
为此，我使用递归 CTE 创建了Calendar_Table，如下所示：

WITH Calendar_CTE AS (
  SELECT CAST('20230101' AS DATE) AS [Date]
  UNION ALL
  SELECT DATEADD(DAY, 1, [Date]) FROM Calendar_CTE
  WHERE DATEADD(DAY, 1, [Date]) < '20240101'
)
SELECT *
INTO Calendar_Table
FROM Calendar_CTE
ORDER BY [Date]
OPTION (maxrecursion 0)

然后，我使用一些动态 SQL 来聚合和透视我在第 3 行和第 4 行中定义的一系列日期的数据。

DECLARE @tsql VARCHAR(MAX) = ''
DECLARE @pivot_columns VARCHAR(MAX) = ''
DECLARE @start_date DATE = '20230901'
DECLARE @end_date DATE = '20230905'
DECLARE @c_date DATE

DECLARE dates_cursor CURSOR FOR
SELECT [Date]
FROM Calendar_Table
WHERE
  [Date] >= @start_date
  AND
  [Date] <= @end_date

OPEN dates_cursor
FETCH NEXT FROM dates_cursor INTO @c_date
WHILE (@@FETCH_STATUS = 0)
BEGIN
  SET @pivot_columns += '[' + CONVERT(VARCHAR(10),@c_date, 23) + '],'
  FETCH NEXT FROM dates_cursor INTO @c_date
END
CLOSE dates_cursor
DEALLOCATE dates_cursor

SET @pivot_columns = LEFT(@pivot_columns, LEN(@pivot_columns) - 1)

SET @tsql += 'SELECT
  PersonalNr,' + @pivot_columns

SET @tsql += '
FROM
(
SELECT
  T1.PersonalNr,
  CT.[Date],
  COUNT(*) AS ct
FROM
  table1 AS T1
LEFT JOIN
  table2 AS T2
  ON T1.PersonalNr = T2.PersonalNr
RIGHT JOIN
  Calendar_Table AS CT
  ON T2.Date = CT.Date
GROUP BY
  T1.PersonalNr, CT.[Date]
) AS Source_Table
PIVOT
(
  COUNT(ct)
  FOR [Date] IN (' + @pivot_columns + ')
) AS Pivot_Table
WHERE
PersonalNr IS NOT NULL'

EXEC (@tsql)

在这里演示

这种方法虽然比上面描述的方法更复杂，但给了我几个好处：

• 它让我可以在我想要的时间段内动态过滤数据
• 我不必为我想在结果/报告/任何地方看到的每一天手动编写聚合
• 我可以轻松修改查询，因为我想看到的日期越多，查询不会越大

话虽如此，我并不完全满意使用光标，可能有更好的方法可以做到这一点并避免使用光标。但考虑到光标将循环的数据集非常小，我并不真正关心它。但仍然......它可以更好。

我希望这对你有所帮助！

关于这个答案： 1：
它没有考虑任何SQL注入漏洞。这是单一解决方案的演示。在不了解 SQL 注入漏洞的情况下，请勿在任何生产系统中使用此代码。
2：它不使用游标或 SQL Server 中的 PIVOT 函数。
3：这是使用 SQL Server 2022 完成的

--  procedurally create a pivot table without PIVOT function and without CURSORS

-- make sure we start with a clean slate
drop table if exists #unique_dates  -- this holds unique personal nr dates to use as columns
drop table if exists #result  
drop table if exists #personal_nr
drop table if exists #personal_nr_date

create table #personal_nr  -- OP table - assumes primary key
(
    personal_nr int not null primary key
)

create table #personal_nr_date  -- OP table - assumes primary key
(
    personal_nr int not null,
    date_created date not null,
    primary key (personal_nr, date_created)
)

create table #result
(
    personal_nr int not null primary key
)

create table #unique_dates
(
    date_created date not null,
    rownum int not null
)

-- insert test data reflecting OP's data
insert into #personal_nr
select  1 union all 
select  2 union all
select  3 union all
select  4 union all
select  5 union all
select  6 union all
select  7

insert into #personal_nr_date
select  1, '2023-09-01' union all
select  2, '2023-09-01' union all
select  3, '2023-09-01' union all
select  4, '2023-09-01' union all
select  5, '2023-09-01' union all
select  6, '2023-09-01' union all
select  2, '2023-09-02' union all
select  3, '2023-09-02' union all
select  4, '2023-09-02' union all
select  5, '2023-09-02' union all
select  6, '2023-09-02' union all
select  1, '2023-09-03' union all
select  6, '2023-09-03' union all
select  7, '2023-09-03'


-- prime the result table with personal_nr values - order by is not required
insert into #result
select  p.personal_nr
from    #personal_nr p  
order by    p.personal_nr


-- we want to store the unique dates with a row number to use with looping
insert into #unique_dates
select  ud.date_created,
        ROW_NUMBER() over (order by ud.date_created) as rownum      
from    #personal_nr_date ud
group by    ud.date_created
order by    ud.date_created asc


-- declare vars for our loop
declare @num_loops int = (select count(1) from #unique_dates)
declare @i int = 1
declare @date_as_column nvarchar(12) = null
declare @previous_date_as_column nvarchar(10) = null
declare @date_var date = null
declare @sql nvarchar(max) = null


-- loop to build out the result table and add to it our personal_nr values
while @i <= @num_loops
    begin
        
        -- get our date column value and store the date for comparison later
        select  @date_as_column = format(udate.date_created, 'yyyy-MM-dd'),
                @date_var = udate.date_created
        from    #unique_dates udate
        where   udate.rownum = @i
        

        -- create dynamic sql to alter the temp table to add the column and values 
        -- NOTICE the default 0 - we update that value if we need to down below
        set @sql =  'alter table #result
                     add '+ quotename(@date_as_column) +' bit not null default 0'
        
        -- uncomment if you want to see the sql 
        -- select @sql
        exec(@sql)
        
        
        -- set 1 for personal_nr values with this date
        set @sql = 'update  r
                    set     r.' + quotename(@date_as_column) +' = 1
                    from    #result r
                                inner join
                            #personal_nr_date d on
                                    r.personal_nr = d.personal_nr
                    where   format(d.date_created, ''yyyy-MM-dd'') = ''' + format(@date_var, 'yyyy-MM-dd') + ''''
        
        exec(@sql)              
        
        -- uncomment if you want to see the sql
        -- select @sql
                                                
        -- increment our loop var
        set @i = @i + 1
    end

    select * from #result


-- do not forget to clean up our mess!                  
drop table if exists #unique_dates  
drop table if exists #result  
drop table if exists #personal_nr
drop table if exists #personal_nr_date