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?

As per the question guide, please show what you’ve tried and tell us what you found (on this site or elsewhere) and why it didn’t meet your needs. — Dale K, May 31 '22 at 20:26
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

score -1 · Answer 2 · answered May 31 '22 at 20:48

This should do what you want.

SELECT f.UserID, (SELECT top(1) Permission from Table_2 t where t.UserID = f.UserID order by Permission desc) as Permit1
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 1 row FETCH NEXT 1 ROWS ONLY) as Permit2
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 2 row FETCH NEXT 1 ROWS ONLY) as Permit3
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 3 row FETCH NEXT 1 ROWS ONLY) as Permit4
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 4 row FETCH NEXT 1 ROWS ONLY) as Permit5
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 5 row FETCH NEXT 1 ROWS ONLY) as Permit6
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 6 row FETCH NEXT 1 ROWS ONLY) as Permit7
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 7 row FETCH NEXT 1 ROWS ONLY) as Permit8
from Table1 as f

I put in your data, and added one more entry for user id 2 and it returned like this.

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

This would work but not sure I would want to query the same data over and over. On a larger dataset this is going to suffer some performance issues for sure. — Sean Lange, May 31 '22 at 21:18

Filling fields with Null values

2 Answers2