Transpose data for a fixed number of rows, supplying defaults where data doesn't exist?

Question

I have a table that stores version information on particular accounts. Below is the structure and some sample data:

CREATE TABLE #Account 
(
    [Account No] int,
    [User] varchar(50),
    [Version No] tinyint,
    [Version Comment] 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')

I need to transpose that data to look like this (using Account No. 3 as an example):

Username1	Username2	...	Username20	Title1	Title2	...	Title20	Comment1	Comment2	...	Comment20
User	Admin	...	Some Default Value	Version #1 - 3/3/2024 8:00 AM	Version #2 - 3/3/2024 12:00 PM	...	Some Default Value	New Account	Edit Account	...	Some Default Value

In other words, for a particular account number, I need to...

1. Use data from the table where a version exists and supply a default value where it doesn't, for 20 versions (it will always be 20 versions)

2. Transpose this data, appending the version number to the column name.

Ideally, I understand that this data transformation would be done by the front-end system of the database, but in this case I need SQL to do it.

I tried this originally:

DECLARE @AccountNo int = 3

-- Get the name of the users that have created each version
SELECT CONCAT('Username', [Version No]) 'Name',
        [User] 'Value'
FROM #Account
WHERE [Account No] = @AccountNo

UNION ALL

-- Get the title of each version
SELECT CONCAT('Title', [Version No]) 'Name',
        CONCAT('Version #', [Version No], ' - ', FORMAT(CONVERT(datetime2(2), [Date] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 'mm/dd/yyyy hh:mm tt')) 'Value'
FROM #Account
WHERE [Account No] = @AccountNo

UNION ALL

-- Get the comment left for each version
SELECT CONCAT('Comment', [Version No]) 'Name',
        [Version Comment] 'Value'
FROM #Account
WHERE [Account No] = @AccountNo

But this doesn't supply me the default values and it requires the data to be transposed. I've also looked at other options (or combination of options), such as...

• PIVOT/UNPIVOT
• SQL sequences w/ some kind of join (to fill in the remaining values up to 20)
• CASE expressions
• Multiple IF statements

...but I have yet to create something that works, much less is performant. Thoughts?

EDIT:

• I am using SQL Server 2022
• This is a helpful similar question, per the comments.

Answer 1

I would suggest you do this in the presentation layer instead of the database. But sometimes that isn't possible. In this case, because the number of column groups is always going to be the same you can use conditional aggregation. I have no idea what your output column named "Title" is from the sample data so I will let you sort that out. I demonstrated the first 5 columns in a group so you can see the technique. I will let you copy and paste for all 20 repeating groups.

Also, not sure which version of sql server you are using so I assumed 2022. The generate_series function is not available in earlier versions. I just used to generate a table with numbers 1 - 20 easily. There are lots of other ways to tackle that part.

with SortedVals as
(
    select *
        , RowNum = ROW_NUMBER() over(partition by [Account No] order by [Date])
    from #Account
)
select sv.[Account No]
    , UserName1 = max(case when x.value = 1 then sv.[User] end)
    , UserName2 = max(case when x.value = 2 then sv.[User] end)
    , UserName3 = max(case when x.value = 3 then sv.[User] end)
    , UserName4 = max(case when x.value = 4 then sv.[User] end)
    , UserName5 = max(case when x.value = 5 then sv.[User] end)
    , Comment1 = max(case when x.value = 1 then sv.[Version Comment] end)
    , Comment2 = max(case when x.value = 2 then sv.[Version Comment] end)
    , Comment3 = max(case when x.value = 3 then sv.[Version Comment] end)
    , Comment4 = max(case when x.value = 4 then sv.[Version Comment] end)
    , Comment5 = max(case when x.value = 5 then sv.[Version Comment] end)
from generate_series(1, 20, 1) x
left join SortedVals sv on sv.RowNum = x.value
where sv.[Account No] is not null
group by sv.[Account No]

Answer 2

Of course, the purpose of such a 60-column table is unclear. However, as a task it is interesting.
I will present a 4-column option that is easily and understandably expandable to the desired number of columns.

1.Unpivot with CROSS APPLY

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

...

2.Pivot

See example

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') VersionCommnet3
  ,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;  

Output

AccountNo	UserName1	UserName2	UserName3	UserName20	VersionNo1	VersionNo2	VersionNo3	VersionNo20	Version Comment1	Version Comment2	Version Comment3	Version Comment20
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

fiddle