SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table Employees (
  EmployeeID int IDENTITY(1,3) NOT NULL,
  employeeName nvarchar(150) NULL,
  empTitle nvarchar(100) NULL,
  email nvarchar(100) NULL)
CREATE Table DateDetails (
  dateID int IDENTITY (1,1) NOT NULL,
  employeeID int NULL,
  dateCreated datetime NULL)
INSERT INTO Employees (employeeName, empTitle, email)
VALUES
  ('Jane Doe','Senior Assistant Attorney','jdoe@hotmail.com'),
('Karen Wise','Internal Auditor','kwise@yahoo.com'),
('Angel Marist','Director, Library','Amarist@gmail.com'),
('Kevin Wiseman','Library Director','	kwiseman@yahoo.com'),
('Major Bates','Director','mbates@bing.com'),
('May Baker','PRINCIPAL AUDITOR','mbaker@outlook.com'),

  ('Jane Doe','Senior Assistant Attorney','jdoe@hotmail.com'),
('Karen Wise','Internal Auditor','kwise@yahoo.com'),
('Angel Marist','Director, Library','Amarist@gmail.com'),
('Kevin Wiseman','Library Director','	kwiseman@yahoo.com'),
('Major Bates','Director','mbates@bing.com'),
('May Baker','PRINCIPAL AUDITOR','mbaker@outlook.com'),

  ('Jane Doe','Senior Assistant Attorney','jdoe@hotmail.com'),
('Karen Wise','Internal Auditor','kwise@yahoo.com'),
('Angel Marist','Director, Library','Amarist@gmail.com'),
('Kevin Wiseman','Library Director','	kwiseman@yahoo.com'),
('Major Bates','Director','mbates@bing.com'),
('May Baker','PRINCIPAL AUDITOR','mbaker@outlook.com'),

  ('Jane Doe','Senior Assistant Attorney','jdoe@hotmail.com'),
('Karen Wise','Internal Auditor','kwise@yahoo.com'),
('Angel Marist','Director, Library','Amarist@gmail.com'),
('Kevin Wiseman','Library Director','	kwiseman@yahoo.com'),
('Major Bates','Director','mbates@bing.com'),
('May Baker','PRINCIPAL AUDITOR','mbaker@outlook.com');
24 rows affected

INSERT INTO DateDetails (employeeID, dateCreated)
VALUES
  (1,'2018-04-19 10:12:29.133'),
(4,NULL),
(7,'2018-04-24 17:22:39.143'),
(10,NULL),
(13,'2018-04-24 11:20:23.103'),
(16,'2018-04-24 12:22:29.133'),

(19,'2019-04-19 10:12:29.133'),
(22,'2019-04-24 12:22:29.130'),
(25,NULL),
(28,'2019-04-16 10:09:29.100'),
(31,'2019-04-24 11:20:23.103'),
(34,NULL),

 (37,NULL),
(40,'2022-04-24 12:22:29.130'),
(43,'2022-04-24 17:22:39.143'),
(46,'2022-04-16 10:09:29.100'),
(49,'2022-04-24 11:20:23.103'),
(52,'2022-04-24 12:22:29.133'),

 (55,'2023-04-19 10:12:29.133'),
(58,'2023-04-24 12:22:29.130'),
(61,'2023-04-24 17:22:39.143'),
(64,'2023-04-16 10:09:29.100'),
(67,'2023-04-24 11:20:23.103'),
(70,'2023-04-24 12:22:29.133');
  
24 rows affected

DECLARE @cols AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy')) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @cols;


(No column name)


[2018],[2019],[2022],[2023]


select employeeName
, empTitle
, email
, YEAR(dateCreated) AS YearCreated
, CASE WHEN dateCreated IS NULL THEN 'No Data' ELSE 'Submitted' END AS Submitted 
from 
-- SourceDetails 
-- inner join 
Employees 
-- on SourceDetails.employeeID = Employees.EmployeeID
inner join 
DateDetails 
on Employees.employeeID = DateDetails.employeeID


employeeName
empTitle
email
YearCreated
Submitted


Jane Doe
Senior Assistant Attorney
jdoe@hotmail.com
2018
Submitted

Karen Wise
Internal Auditor
kwise@yahoo.com
null
No Data

Angel Marist
Director, Library
Amarist@gmail.com
2018
Submitted

Kevin Wiseman
Library Director
 kwiseman@yahoo.com
null
No Data

Major Bates
Director
mbates@bing.com
2018
Submitted

May Baker
PRINCIPAL AUDITOR
mbaker@outlook.com
2018
Submitted

Jane Doe
Senior Assistant Attorney
jdoe@hotmail.com
2019
Submitted

Karen Wise
Internal Auditor
kwise@yahoo.com
2019
Submitted

Angel Marist
Director, Library
Amarist@gmail.com
null
No Data

Kevin Wiseman
Library Director
 kwiseman@yahoo.com
2019
Submitted

Major Bates
Director
mbates@bing.com
2019
Submitted

May Baker
PRINCIPAL AUDITOR
mbaker@outlook.com
null
No Data

Jane Doe
Senior Assistant Attorney
jdoe@hotmail.com
null
No Data

Karen Wise
Internal Auditor
kwise@yahoo.com
2022
Submitted

Angel Marist
Director, Library
Amarist@gmail.com
2022
Submitted

Kevin Wiseman
Library Director
 kwiseman@yahoo.com
2022
Submitted

Major Bates
Director
mbates@bing.com
2022
Submitted

May Baker
PRINCIPAL AUDITOR
mbaker@outlook.com
2022
Submitted

Jane Doe
Senior Assistant Attorney
jdoe@hotmail.com
2023
Submitted

Karen Wise
Internal Auditor
kwise@yahoo.com
2023
Submitted

Angel Marist
Director, Library
Amarist@gmail.com
2023
Submitted

Kevin Wiseman
Library Director
 kwiseman@yahoo.com
2023
Submitted

Major Bates
Director
mbates@bing.com
2023
Submitted

May Baker
PRINCIPAL AUDITOR
mbaker@outlook.com
2023
Submitted


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy')) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT employeeName
                   , empTitle
                   , email, 
                   ' + @cols + ' 
                   from 
            (
                select employeeName
                    , empTitle
                    , email
                    , YEAR(dateCreated) AS YearCreated
                    , ''Submitted'' AS Submitted 
                from 
--                     SourceDetails 
--                inner join 
                     Employees 
--                       on SourceDetails.employeeID = Employees.EmployeeID
                inner join 
                     DateDetails 
                       on Employees.employeeID = DateDetails.employeeID
           ) x
            pivot 
            (
                 Max(Submitted)
                for YearCreated in (' + @cols + ')
            ) p ';

EXECUTE sp_executesql @query


employeeName
empTitle
email
2018
2019
2022
2023


Angel Marist
Director, Library
Amarist@gmail.com
Submitted
null
Submitted
Submitted

Jane Doe
Senior Assistant Attorney
jdoe@hotmail.com
Submitted
Submitted
null
Submitted

Karen Wise
Internal Auditor
kwise@yahoo.com
null
Submitted
Submitted
Submitted

Kevin Wiseman
Library Director
 kwiseman@yahoo.com
null
Submitted
Submitted
Submitted

Major Bates
Director
mbates@bing.com
Submitted
Submitted
Submitted
Submitted

May Baker
PRINCIPAL AUDITOR
mbaker@outlook.com
Submitted
null
Submitted
Submitted

employeeName	empTitle	email	YearCreated	Submitted
Jane Doe	Senior Assistant Attorney	jdoe@hotmail.com	2018	Submitted
Karen Wise	Internal Auditor	kwise@yahoo.com	null	No Data
Angel Marist	Director, Library	Amarist@gmail.com	2018	Submitted
Kevin Wiseman	Library Director	kwiseman@yahoo.com	null	No Data
Major Bates	Director	mbates@bing.com	2018	Submitted
May Baker	PRINCIPAL AUDITOR	mbaker@outlook.com	2018	Submitted
Jane Doe	Senior Assistant Attorney	jdoe@hotmail.com	2019	Submitted
Karen Wise	Internal Auditor	kwise@yahoo.com	2019	Submitted
Angel Marist	Director, Library	Amarist@gmail.com	null	No Data
Kevin Wiseman	Library Director	kwiseman@yahoo.com	2019	Submitted
Major Bates	Director	mbates@bing.com	2019	Submitted
May Baker	PRINCIPAL AUDITOR	mbaker@outlook.com	null	No Data
Jane Doe	Senior Assistant Attorney	jdoe@hotmail.com	null	No Data
Karen Wise	Internal Auditor	kwise@yahoo.com	2022	Submitted
Angel Marist	Director, Library	Amarist@gmail.com	2022	Submitted
Kevin Wiseman	Library Director	kwiseman@yahoo.com	2022	Submitted
Major Bates	Director	mbates@bing.com	2022	Submitted
May Baker	PRINCIPAL AUDITOR	mbaker@outlook.com	2022	Submitted
Jane Doe	Senior Assistant Attorney	jdoe@hotmail.com	2023	Submitted
Karen Wise	Internal Auditor	kwise@yahoo.com	2023	Submitted
Angel Marist	Director, Library	Amarist@gmail.com	2023	Submitted
Kevin Wiseman	Library Director	kwiseman@yahoo.com	2023	Submitted
Major Bates	Director	mbates@bing.com	2023	Submitted
May Baker	PRINCIPAL AUDITOR	mbaker@outlook.com	2023	Submitted