CODEWITHSUNDEEP
sqlsql-serverselectdistinct
Eskifan
Eskifan

Reputation: 21

Separate distinct values in to columns

Hi all I am quite new to SQL. I have a table (TABLE1) with two columns as below 

Name age
--------
jim  18
jim  21
dave 18
dave 18
john 23
john 41

I need to create a view in SSMS which lists distinct ages for each name in a separate column as below 

Jim  Dave  John
---------------
18   18     23
21          41

I have tried a sub query like

SELECT DISTINCT AGE FROM TABLE1 WHERE NAME = JIM

But I encountered a sub query cannot return more than one value.

Upvotes: 1

Views: 888

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() & do aggregation : 

select max(case when name = 'jim' then age end) as jim,
       max(case when name = 'dave' then age end) as dave,
       max(case when name = 'john' then age end) as john
from (select t.*, row_number() over (partition by name order by age) as seq
      from table t
     ) t
group by seq;

Upvotes: 1

Related Questions