-1

I have two tables: pet type

Id TypeB
1 dog
2 cat
3 snake

Then another owners

Ownerid Pettype
001 1
002 2
003 3
004 0

I am trying to get the values those owners who have pets as in this format

Ownerid Cat Dog snake
001 1 0 0
002 0 1

It should display like this format I tried using joins but couldn’t can any one help me out

I tried using join of three tables

Kalpana K Kalpana
  • 1
  • 1
  • Moving values between rows & headings is pivoting/unpivoting. PS Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research. [ask] [Help] [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) – philipxy Mar 26 '22 at 02:22
  • Ownerid=1 should have Dog=1 and Ownerid=2 should have cat=1 – RF1991 Mar 26 '22 at 05:43

1 Answers1

0

use pivot as follows

SELECT *
FROM
(
select Ownerid,TypeB from Ownerstable o
join pettype p
on p.Id=o.Pettype
) d  
PIVOT
(
    count(TypeB)
    FOR TypeB IN ([cat],[dog],[snake])
) p
RF1991
  • 2,037
  • 4
  • 8
  • 17