-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathOccupations.sql
110 lines (96 loc) · 3.01 KB
/
Occupations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
/*
https://www.hackerrank.com/challenges/occupations/problem
*/
select min(temp.Doctor), min(temp.Professor),min(temp.Singer), min(temp.Actor)
from(
select
name,
ROW_NUMBER() OVER(PARTITION By Doctor,Professor,Singer,Actor order by name asc) AS Rownum,
case when Doctor=1 then name else Null end as Doctor,
case when Professor=1 then name else Null end as Professor,
case when Singer=1 then name else Null end as Singer,
case when Actor=1 then name else Null end as Actor
from
occupations
pivot (count(occupation) for occupation in(Doctor, Professor, Singer, Actor)) as p
) temp
group by temp.Rownum;
/*
====Steps 1====
select
name, Doctor, Professor, Singer, Actor
from
occupations
pivot (count(occupation) for occupation in(Doctor, Professor, Singer, Actor)) as p
Out put:
Aamina 1 0 0 0
Ashley 0 1 0 0
Belvet 0 1 0 0
Britney 0 1 0 0
Christeen 0 0 1 0
Eve 0 0 0 1
Jane 0 0 1 0
Jennifer 0 0 0 1
Jenny 0 0 1 0
Julia 1 0 0 0
Ketty 0 0 0 1
Kristeen 0 0 1 0
Maria 0 1 0 0
Meera 0 1 0 0
Naomi 0 1 0 0
Priya 1 0 0 0
Priyanka 0 1 0 0
Samantha 0 0 0 1
====Steps 2====
select
name,
ROW_NUMBER() OVER(PARTITION By Doctor,Professor,Singer,Actor order by name asc) AS Rownum,
case when Doctor=1 then name else Null end as Doctor,
case when Professor=1 then name else Null end as Professor,
case when Singer=1 then name else Null end as Singer,
case when Actor=1 then name else Null end as Actor
from
occupations
pivot (count(occupation) for occupation in(Doctor, Professor, Singer, Actor)) as p
Out put:
Eve 1 NULL NULL NULL Eve
Jennifer 2 NULL NULL NULL Jennifer
Ketty 3 NULL NULL NULL Ketty
Samantha 4 NULL NULL NULL Samantha
Christeen 1 NULL NULL Christeen NULL
Jane 2 NULL NULL Jane NULL
Jenny 3 NULL NULL Jenny NULL
Kristeen 4 NULL NULL Kristeen NULL
Ashley 1 NULL Ashley NULL NULL
Belvet 2 NULL Belvet NULL NULL
Britney 3 NULL Britney NULL NULL
Maria 4 NULL Maria NULL NULL
Meera 5 NULL Meera NULL NULL
Naomi 6 NULL Naomi NULL NULL
Priyanka 7 NULL Priyanka NULL NULL
Aamina 1 Aamina NULL NULL NULL
Julia 2 Julia NULL NULL NULL
Priya 3 Priya NULL NULL NULL
====Steps 2====
// added rownum to make the result understandable.
select Rownum, min(Doctor), min(Professor),min(Singer), min(Actor)
from(
select
name,
ROW_NUMBER() OVER(PARTITION By Doctor,Professor,Singer,Actor order by name asc) AS Rownum,
case when Doctor=1 then name else Null end as Doctor,
case when Professor=1 then name else Null end as Professor,
case when Singer=1 then name else Null end as Singer,
case when Actor=1 then name else Null end as Actor
from
occupations
pivot (count(occupation) for occupation in(Doctor, Professor, Singer, Actor)) as p
) temp
group by Rownum ;
1 Aamina Ashley Christeen Eve
2 Julia Belvet Jane Jennifer
3 Priya Britney Jenny Ketty
4 NULL Maria Kristeen Samantha
5 NULL Meera NULL NULL
6 NULL Naomi NULL NULL
7 NULL Priyanka NULL NULL