Search code examples
sql-servert-sqltranspose

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

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.
Solution

  • 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]