CODEWITHSUNDEEP
sqlsql-server
SSilicon
SSilicon

Reputation: 253

Select Columns each with specific where condition in SQL

I have a table contains all the name with different class, like this:

Name   Class

Jack    A

Nick    B

Simon   C

David   B

Linda   B

Alice   C

Right now I want to get a table that with class A, B , C as columns, which contains the names inside their respect class:

A----------B----------C

Jack------Nick-------Simon

----------David------Alice

----------Linda-----------

How do I get such table in SQL query? Sorry for bad format, don't know how to create table in SO.

Upvotes: 0

Views: 60

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

The data you're asking for isn't really very relational, and in those cases you'll usually get better results from doing the work in your client application. But as that's often not possible:

SELECT A.Name as A, B.Name as B, C.Name AS C
FROM 
  (select Name, row_number over (order by name) as ordinal from table where class = 'A') A
FULL JOIN
  (select Name, row_number over (order by name) as ordinal from table where class = 'B') B
     ON B.ordinal = A.ordinal
FULL JOIN
  (select Name, row_number over (order by name) as ordinal from table where class = 'C') C
     ON C.ordinal = coalesce(A.ordinal, B.ordinal)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is a method that uses aggregation. It is probably the easiest method:

select max(case when class = 'a' then name end) as a,
       max(case when class = 'b' then name end) as b,
       max(case when class = 'c' then name end) as c
from (select name, class, row_number() over (partition by class order by (select NULL)) as seqnum
      from nameclasses
     ) nc
group by seqnum
order by seqnum;

This is the original method that I posted. It doesn't use aggregation, but it does a lot of joins:

select a.name as a, b.name as b, c.name as c
from (select name, row_number() over (order by (select NULL)) as seqnum
      from nameclasses nc
      where class = 'A'
     ) a full outer join
     (select name, row_number() over (order by (select NULL)) as seqnum
      from nameclasses nc
      where class = 'B'
     ) b
     on a.seqnum = b.seqnum full outer join
     (select name, row_number() over (order by (select NULL)) as seqnum
      from nameclasses nc
      where class = 'c'
     ) c
     on c.seqnum = coalesce(a.seqnum, b.seqnum)
order by coalesce(a.seqnum, b.seqnum, c.seqnum);

Upvotes: 3

Jean-Daniel Genest
Jean-Daniel Genest

Reputation: 1

Use the "group by" function at the end of your request like this: "select a.toto, a.tata from a group by a.tata". http://www.w3schools.com/sql/sql_groupby.asp

Upvotes: -2

Related Questions