Query same column from 2 rows with different conditions

Transact-SQL
1

HI all, I have the following sample data:

OFFENSENO TYPE REPOFFICER STATUS
100 I John Smith null
100 S Jane Doe 2

I want to return the OFFENSENO, REPOFFICER where the type = 1 followed by the REPOFFICER where the type = S and STATUS = 2.

i.e. SELECT offenseno, repofficer (where type = 1), repofficer (where type = S and status =2)

Thanks so much for any help you could provide. I appreciate it so much.

2

I assume you are joining on OffenseNo?? What happens if there is more than 1 matching record? You could try a CTE

create table #Temp(
OffenseNo int,
Type char(1),
RepOfficer varchar(30),
Status tinyint
)

Insert into #Temp(OFFENSENO, TYPE, REPOFFICER, STATUS)
values
(100,'1','John Smith', null),
(100,'S','Jane Doe',2)


;With cte1 as
(select OffenseNo, Type, RepOfficer, Status
  from #Temp
  where Type = '1'),
cte2 as 
(select OffenseNo, Type, RepOfficer, Status
  from #Temp
  where Type = 'S'
   and Status = 2)

select * 
  from cte1 c
	join cte2 c2
		on c.OffenseNo = c2.OffenseNo
3 Likes
3

Hi Mike,

I am querying only from this table, but OFFENSENO is the foreign key.There will be several occurrences of the same OFFENSENO, but the other fields will be unique.

4

Try this:

select offenseno
      ,repofficer
  from yourtable
 where [type]='1'
    or ([type]='S'
   and  [status]=2
       )
;
5

Thanks but it needs to return both REPOFFICERs on each row, as my pseudocode shows above.

There ought to be a query that would do this for the entire table instead of creating a temporary table and importing all the rows. Thanks.

6

Well then I misunderstood what you were trying to do. The solution from @mike01 does the job :slight_smile: You "just" have to use the query starting from ";with cte1 as" and replace #temp with your table name.

1 Like
7

Thanks so much. Mike's solution does work for me.

1 Like