CODEWITHSUNDEEP
c#sqlsql-serverpivot
Art Man
Art Man

Reputation: 11

SQL converting rows into columns without pivot

I have a table named STUDENTS with columns (LNAME, FNAME, PROGRAM) all VARCHAR. Here are the initial results with values added using MS SQL Server 2014:

TABLE STUDENTS:

LNAME-------FNAME-------PROGRAM

LEGEND-----MARK--------------IT

SANCHEZ----ERIC---------------IT

RHODES-----JOHN--------------CS

YOUNG-------ART----------------CS

SPARKS------CATH--------------IS

BATUM--------TIM------------------IS

I want my table to look like this:

IT-----------------------------------------CS-----------------------------------------IS

LEGEND,MARK---------------RHODES,JOHN-----------------------SPARKS,CATH

SANCHEZ,ERIC-----------------YOUNG,ART-----------------------------BATUM,TIM

Any ideas?

Upvotes: 1

Views: 2219

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I tend to approach these problems using conditional aggregation. The problem is that you don't have a "line number" for sorting. In SQL Server (and most other databases) you can use row_number():

select max(case when program = 'IT' then lname + ',' + fname end) as prog_it,
       max(case when program = 'CS' then lname + ',' + fname end) as prog_cs,
       max(case when program = 'IS' then lname + ',' + fname end) as prog_is       
from (select s.*,
             row_number() over (partition by program order by (select null)) as seqnum
      from students s
     ) s
group by seqnum;

You can do something similar in MySQL, but it looks quite different.

Upvotes: 4

Related Questions