CODEWITHSUNDEEP
sql-servert-sqlpivotpivot-table
Dave
Dave

Reputation: 8631

Using PIVOT with SQL Server without Aggregate function

I'm stuck on using PIVOT in a simple example (which I give in entirety below). Full disclosure, I got this from https://www.hackerrank.com/. I picked it precisely because I want to get more familiar with PIVOT and this looked like a simple example! I've looked at numerous posts on the subject, and have been using this to crib off: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b76a4668-d0c3-4c51-8d86-117d5c181e69/pivot-without-aggregate-function?forum=transactsql but don't seem to be able to get things quite right. Here is the table:

 TABLE OCCUPATIONS

Name    Occupation
Samantha    Doctor
Julia   Actor
Maria   Actor
Meera   Singer
Ashley  Professor
Ketty   Professor
Christeen   Professor
Jane    Actor
Jenny   Doctor
Priya   Singer

The task is to have the output with columns Doctor, Professor, Singer or Actor (in that order). If you run out of data for one or more columns, put NULL. Here is the expected output (copied directly from the site).

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

As an aside, it appears they want the results without column headers (I'm not sure!).

Here is the latest iteration of what I have tried:

SELECT [Doctor], [Professor],[Singer], [Actor]
FROM
(SELECT [Name], [Occupation] from OCCUPATIONS) as pvtsource
PIVOT
( MAX([Name]) FOR [Occupation] IN ([Doctor], [Professor],[Singer], [Actor]) ) AS p

and it yields:

     Doctor Professor   Singer  Actor
Samantha    Ketty   Priya   Maria

I'm not surprised by this incorrect result. After all, I did say in my query MAX. I assume it's just picking the MAX name for each profession based on the alphabetical sort. Maria is a "bigger" actor than Julia or Jane for example if you based it on the alphabet. But when I remove the MAX, I get an error ("Incorrect syntax..."). How does one do this?

Thanks! Bonus questions 1. Good, gentle, articles to PIVOT? I clearly haven't gotten it through my thick head. Eventually, I do want to be able to do more complicated pivots where I SUM or take MAX. 2. How to display results without column headers? 3. I'd also be interested in how to do this without PIVOT if there is a simple way.

Upvotes: 1

Views: 163

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

You need to "FEED" the pivot with an X-Axis,Y-Axis and a Value. We create a row key via dense_rank()

Example

Declare @YourTable Table ([Name] varchar(50),[Occupation] varchar(50))  Insert Into @YourTable Values 
 ('Samantha','Doctor')
,('Julia','Actor')
,('Maria','Actor')
,('Meera','Singer')
,('Ashley','Professor')
,('Ketty','Professor')
,('Christeen','Professor')
,('Jane','Actor')
,('Jenny','Doctor')
,('Priya','Singer')

Select * 
 from (Select *
             ,RN = dense_rank() over (partition by occupation order by name)
        From  @YourTable
      ) src
 Pivot (max(Name) for Occupation in ([Doctor], [Professor],[Singer], [Actor]) ) pvt

Returns

RN  Doctor      Professor   Singer  Actor
1   Jenny       Ashley      Meera   Jane
2   Samantha    Christeen   Priya   Julia
3   NULL        Ketty       NULL    Maria

NOTE:

If you don't want RN in your results, rather than the top SELECT *, you can specify the desired columns

SELECT [Doctor], [Professor],[Singer], [Actor]
 From  (...) src
 Pivot (...) pvt

EDIT - Commentary

If you run the inner query

Select *
      ,RN = dense_rank() over (partition by occupation order by name)
 From  @YourTable
 Order By RN

You'll get

Name        Occupation  RN
Jane        Actor       1
Jenny       Doctor      1
Ashley      Professor   1
Meera       Singer      1
Priya       Singer      2
Christeen   Professor   2
Samantha    Doctor      2
Julia       Actor       2
Maria       Actor       3
Ketty       Professor   3

RN becomes the Y-Axis, Occupation becomes the X-Axis and Name is the value. Pivots by design are aggregates, therefore we just need a Y-Axis to perform the group by.

Upvotes: 2

Related Questions