CODEWITHSUNDEEP
sqlsql-serverdatabasepivot
Asynchronous
Asynchronous

Reputation: 3977

Using Row Number to Partition data

Okay this is one of those situations where I have concluded that I am lost.

I have a table that looks like this:

enter image description here

I have written the following code:

WITH CTE AS(
SELECT 
 USER_NBR
,CASE WHEN MOVIE_TYPE = 'Action'      THEN MOVIE_NAME END [ACTION]                        
,CASE WHEN MOVIE_TYPE = 'Drama'       THEN MOVIE_NAME END DRAMA
,CASE WHEN MOVIE_TYPE = 'Romance'     THEN MOVIE_NAME END ROMANCE
,ROW_NUMBER() OVER (PARTITION BY  USER_NBR ORDER BY  USER_NBR) AS OCCURANCE
FROM dbo.MOVIE)

SELECT * FROM CTE WHERE OCCURANCE = 1

I get this result:

enter image description here

What I don't understand is why am I getting Nulls in the query result?

What am I doing wrong? As you can see I am categorizing the movies.

Thanks for checking this out

Upvotes: 0

Views: 937

Answers (2)

McGarnagle
McGarnagle

Reputation: 102743

Maybe this approach will work for you. In general when you're turning row values into columns (like movie types in your example), it's a pivot function (ref http://msdn.microsoft.com/en-us/library/ms177410.aspx)

select * from 
(select user_nbr, movie_name, movie_type from movies) as movies
pivot 
(max(movie_name)
    for movie_type in ([Action], [Drama], [Romance])
)
as PivotTable

Result:

user_nbr   Action             Drama           Romance
101        Casino Royale    Pretty Woman    Love Actually
102        Casino Royale    Pretty Woman    Love Actually

Upvotes: 3

pratik garg
pratik garg

Reputation: 3342

please note that if you are trying to get this type of output the you have to use group by clause ..

say if you want to get value for all the category then you can try following query .. 

WITH CTE AS(
SELECT 
 USER_NBR
,CASE WHEN MOVIE_TYPE = 'Action'      THEN MOVIE_NAME END [ACTION]                        
,CASE WHEN MOVIE_TYPE = 'Drama'       THEN MOVIE_NAME END DRAMA
,CASE WHEN MOVIE_TYPE = 'Romance'     THEN MOVIE_NAME END ROMANCE
,count(*) AS OCCURANCE
FROM dbo.MOVIE
 group by USER_NBR
)
SELECT * FROM CTE

I think this is what you are trying to do ..

Upvotes: 0

Related Questions