Retrieving multiple rows using Pivot in SQL

Question

I am having difficulty in understanding the below SQL query to pivot the table,

Table Name : Occupations

Name    | Occupation
-----------------
Sam     | Actor
Julia   | Singer
Ketty   | Actor

Expected result:

Actor   | Singer
----------------
Sam     | Julia
Ketty   | null

I got only one row, When I executed the below query,

Select [Actor],[Singer] From ( Select Occupation,Name From Occupations
 )sub Pivot (Max(Name) For Occupation in ([Actor],[Singer])) pvt

output :

Actor   | Singer
----------------
Sam | Julia

When I modified the above query using RowNumber(), I got the expected result(multiple rows).

 Select [Actor],[Singer] 
 From ( Select Occupation,Name,Row_Number() over(partition by Occupation order by Name)SNo
 From Occupations )sub
 Pivot (Max(Name) For Occupation in ([Actor],[Singer])) pvt

Can you explain , How adding Row_Number function in the sub query gives multiple row?

Your source data is insufficient to pivot, because there is no column which logically connects one actor to another singer. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 14, 2018 at 10:16
Actually, I just want the list of Actor and Singer. If I execute the second query in the question I got the result, But I don't know how it works. — Surya Prakash
– Surya Prakash, Commented May 14, 2018 at 10:22
For this it would better to do google instead of asking the question, just checkout how PIVOT and Row_Number() works ? — Shushil Bohara
– Shushil Bohara, Commented May 14, 2018 at 10:25
@SuryaPrakash The Yogesh answer below should work for you then. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 14, 2018 at 10:27

Gordon Linoff · Accepted Answer · 2018-05-14 10:27:29Z

4

Pivot is a tricky syntax. It returns one row for all the combination of columns other than the columns involved with the pivot. When you do:

Select [Actor], [Singer] 
From (Select Occupation, Name,
      From Occupations
     ) sub
Pivot (Max(Name) For Occupation in ([Actor], [Singer])) pvt

There are no other rows. Hence, one row is returned.

When you add:

row_Number() over (partition by Occupation order by Name) as sno

You now have another column that varies, and it is used (implicitly) by the pivot.

This awkward behavior is one reason why I prefer conditional aggregation to pivot.

answered May 14, 2018 at 10:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Surya Prakash Over a year ago

Thanks Gordon, This is the answer I was looking for.

Yogesh Sharma · Accepted Answer · 2018-05-14 10:29:56Z

1

You want conditional aggregation :

select 
        max(case when Occupation = 'Actor' then Name end) [Actor],
        max(case when Occupation = 'Singer' then Name end) [Singer]
from (select *,
            row_number() over (partition by Occupation order by Name) Seq
      from table t
     ) t
group by Seq;

Since the above data model has no any grouping column/field based on that desired result achieve. So, you can generate the Sequence using analytical function and use them as in GROUPING.

edited May 14, 2018 at 10:29

answered May 14, 2018 at 10:15

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

2 Comments

Surya Prakash Over a year ago

Hi Yogesh, your query works fine. But I just want to achieve the same using pivot.

Yogesh Sharma Over a year ago

@SuryaPrakash... Your pivot query should also work.

Collectives™ on Stack Overflow

Retrieving multiple rows using Pivot in SQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related