Selecting the same field but filter twice into different fields

i have quick question think... i've got field "user name" need to filter field in row. have return 2 values. need user user role - assessing official in 1 column and need user user role - admin in 2nd column.

to first 1 this...

select     tablea.[user role], tablea.[user name]

from        tablea

where     (tablea.[user role] = n'assessing official')

but how change both values in 2 different columns. don't want filter once users assessing officials , admins. want col1 have assessing officials , col2 have admins.

hi,

new data, new query :)

please try this, worked me:

-- demo, uses tempdb use tempdb; go  drop table tablea;  -- create demo table , insert dummy record: create table tablea ([contract number] varchar(50), [user name] varchar(50), [user role] varchar(50)); insert tablea values ('xx-xx-123', 'sam', 'admin'), 	('xx-xx-123', 'tom', 'assessing official'), 	('xx-xx-456', 'jim', 'admin'), 	('xx-xx-456', 'steve', 'assessing official');  select distinct [contract number], 	(select [user name] tablea t t.[contract number] = tablea.[contract number] , t.[user role]='admin') [admin],  	(select [user name] tablea t t.[contract number] = tablea.[contract number] , t.[user role]='assessing official') [assessing official]  tablea

hope helps,

---

please use mark answer if post solved problem , use vote helpful if post useful.

pedro martins
portugal
https://www.linkedin.com/in/rechousa

SQL Server  >  Data Mining