Filling fields with Null values

Question

I have two tables in SQL Server 2012

Table1

UserID	Name
1	Joe
2	Mary

Table2

UserID	Permission
1	P15
2	P5
2	P330

Each user can have between 1 and 8 Permissions.

I need to create a view that will give me the UserID with 8 permission entries with any unused entries containing null values i.e. The order of the entries does not matter.

UserID	Permit1	Permit2	Permit3	Permit4	Permit5	Permit6	Permit7	Permit8
1	P15	Null	Null	Null	Null	Null	Null	Null
2	P5	P330	Null	Null	Null	Null	Null	Null

This has me stumped, I don't even know where to start?

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky, May 31 '22 at 20:30
You also need to let us know how to determine the order of the permits. What makes P5 come before P330 or does order matter? — xQbert, May 31 '22 at 20:32
First use subqueries and create a CROSS JOIN between unique UserID and unique Permits. That'll give you a base to work with. Then outer join THAT result to your real data, and the permits will be NULL for when there wasn't one. Lastly, you PIVOT the result (this is where I'm not sure how SQL Server best does this for text... maybe a bunch of case statements, or maybe you can MAX(permitname) but it might complain since its text) — Josh, May 31 '22 at 20:43

score 4 · Accepted Answer · answered May 31 '22 at 21:16

You can do this easily with conditional aggregation. This has been asked and answered hundreds of times but if it is a new concept it would be hard to know what search terms to use. This is complete with consumable sample data.

declare @Users table
(
    UserID int
    , Name varchar(10)
)

insert @Users values
(1, 'Joe')
, (2, 'Mary')

declare @Permissions table
(
    UserID int
    , Permission varchar(10)
)

insert @Permissions values
(1, 'P15')
, (2, 'P5')
, (2, 'P330')


select x.UserID
    , Permit1 = max(case when x.RowNum = 1 then x.Permission end)
    , Permit2 = max(case when x.RowNum = 2 then x.Permission end)
    , Permit3 = max(case when x.RowNum = 3 then x.Permission end)
    , Permit4 = max(case when x.RowNum = 4 then x.Permission end)
    , Permit5 = max(case when x.RowNum = 5 then x.Permission end)
    , Permit6 = max(case when x.RowNum = 6 then x.Permission end)
    , Permit7 = max(case when x.RowNum = 7 then x.Permission end)
    , Permit8 = max(case when x.RowNum = 8 then x.Permission end)
from
(
    select u.UserID
        , p.Permission
        , RowNum = ROW_NUMBER() over(partition by u.UserId order by p.Permission)
    from @Users u
    join @Permissions p on p.UserID = u.UserID
) x
group by x.UserID

Filling fields with Null values

1 Answers1