SQL Server 2022

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

CREATE TABLE #Account (
    [AccountNo] int,
    [UserName] varchar(50),
    [VersionNo] tinyint,
    [VersionComment] varchar(280),
    [Date] datetime2(2))

INSERT INTO #Account
VALUES (1, 'Admin', 1, 'New Account', '2024-03-01 12:00:00.00'),
       (1, 'User', 2, 'Edit Account - Add name', '2024-03-02 8:00:00.00'),
       (1, 'Admin', 3, 'Edit Account - Fix name', '2024-03-02 11:00:00.00'),
       (2, 'Admin', 1, 'New Account', '2024-03-02 14:00:00.00'),
       (3, 'User', 1, 'New Account', '2024-03-03 8:00:00.00'),
       (3, 'Admin', 2, 'Edit Account - Add website url', '2024-03-03 12:00:00.00')
6 rows affected

select * 
from (select AccountNo,UserName,cast(VersionNo as varchar(10))VersionNo,VersionComment
  ,row_number()over(partition by AccountNo order by [Date])rn 
  from #Account
  ) a
  
cross apply (values(concat('UserName',a.rn),a.UserName)
        ,(concat('VersionNo',a.rn),a.VersionNo)
        ,(concat('VersionComment',a.rn),a.VersionComment)
  )t(AtrName,AtrValue)


AccountNo
UserName
VersionNo
VersionComment
rn
AtrName
AtrValue


1
Admin
1
New Account
1
UserName1
Admin

1
Admin
1
New Account
1
VersionNo1
1

1
Admin
1
New Account
1
VersionComment1
New Account

1
User
2
Edit Account - Add name
2
UserName2
User

1
User
2
Edit Account - Add name
2
VersionNo2
2

1
User
2
Edit Account - Add name
2
VersionComment2
Edit Account - Add name

1
Admin
3
Edit Account - Fix name
3
UserName3
Admin

1
Admin
3
Edit Account - Fix name
3
VersionNo3
3

1
Admin
3
Edit Account - Fix name
3
VersionComment3
Edit Account - Fix name

2
Admin
1
New Account
1
UserName1
Admin

2
Admin
1
New Account
1
VersionNo1
1

2
Admin
1
New Account
1
VersionComment1
New Account

3
User
1
New Account
1
UserName1
User

3
User
1
New Account
1
VersionNo1
1

3
User
1
New Account
1
VersionComment1
New Account

3
Admin
2
Edit Account - Add website url
2
UserName2
Admin

3
Admin
2
Edit Account - Add website url
2
VersionNo2
2

3
Admin
2
Edit Account - Add website url
2
VersionComment2
Edit Account - Add website url


with UserAtr as(
select * 
from (select AccountNo,UserName,cast(VersionNo as varchar(10))VersionNo,VersionComment
  ,row_number()over(partition by AccountNo order by [Date])rn
  ,[date]
  from #Account
  ) a
  
cross apply (values(concat('UserName',a.rn),a.UserName)
        ,(concat('VersionNo',a.rn),concat('Version #',a.VersionNo,'-',[date]))
        ,(concat('VersionComment',a.rn),a.VersionComment)
  )t(AtrName,AtrValue))
SELECT AccountNo,   
  UserName1, UserName2, UserName3,UserName20
  ,isnull(VersionNo1,'def1')VersionNo1,isnull(VersionNo2,'def2')VersionNo2
  ,isnull(VersionNo3,'def3')VersionNo3,isnull(VersionNo20,'def20')VersionNo20
  ,isnull(VersionComment1,'Def value1')VersionComment1
  ,isnull(VersionComment2,'Def value2')VersionComment2
  ,isnull(VersionComment3,'Def value3')VersionComment3
  ,isnull(VersionComment20,'Def value20')VersionComment20
FROM  
(
  SELECT AccountNo, AtrName,AtrValue   
  FROM UserAtr
) AS SourceTable  
PIVOT  
(  
  min(AtrValue)  
  FOR AtrName IN (UserName1, UserName2, UserName3,UserName20
              ,VersionNo1,VersionNo2,VersionNo3,VersionNo20
      ,VersionComment1,VersionComment2,VersionComment3,VersionComment20
  )  
) AS PivotTable;  
  


AccountNo
UserName1
UserName2
UserName3
UserName20
VersionNo1
VersionNo2
VersionNo3
VersionNo20
VersionComment1
VersionComment2
VersionComment3
VersionComment20


1
Admin
User
Admin
null
Version #1-2024-03-01 12:00:00.00
Version #2-2024-03-02 08:00:00.00
Version #3-2024-03-02 11:00:00.00
def20
New Account
Edit Account - Add name
Edit Account - Fix name
Def value20

2
Admin
null
null
null
Version #1-2024-03-02 14:00:00.00
def2
def3
def20
New Account
Def value2
Def value3
Def value20

3
User
Admin
null
null
Version #1-2024-03-03 08:00:00.00
Version #2-2024-03-03 12:00:00.00
def3
def20
New Account
Edit Account - Add website url
Def value3
Def value20

AccountNo	UserName	VersionNo	VersionComment	rn	AtrName	AtrValue
1	Admin	1	New Account	1	UserName1	Admin
1	Admin	1	New Account	1	VersionNo1	1
1	Admin	1	New Account	1	VersionComment1	New Account
1	User	2	Edit Account - Add name	2	UserName2	User
1	User	2	Edit Account - Add name	2	VersionNo2	2
1	User	2	Edit Account - Add name	2	VersionComment2	Edit Account - Add name
1	Admin	3	Edit Account - Fix name	3	UserName3	Admin
1	Admin	3	Edit Account - Fix name	3	VersionNo3	3
1	Admin	3	Edit Account - Fix name	3	VersionComment3	Edit Account - Fix name
2	Admin	1	New Account	1	UserName1	Admin
2	Admin	1	New Account	1	VersionNo1	1
2	Admin	1	New Account	1	VersionComment1	New Account
3	User	1	New Account	1	UserName1	User
3	User	1	New Account	1	VersionNo1	1
3	User	1	New Account	1	VersionComment1	New Account
3	Admin	2	Edit Account - Add website url	2	UserName2	Admin
3	Admin	2	Edit Account - Add website url	2	VersionNo2	2
3	Admin	2	Edit Account - Add website url	2	VersionComment2	Edit Account - Add website url