Flatten few rows into one

Question

Post reply

Flatten few rows into one

barsuk

SSChampion

Points: 12280
More actions
April 8, 2004 at 11:50 am

#107507

Hi All,
I have number of students and six classes. I use SQL Server 7.
In some classes student can have grade or may not have. See below:
Students                       Class1 Class2 Class3 Class4 Class5 Class6
------------------------------ ------ ------ ----   ----- ------- -------
Student1                        A       NULL  NULL   NULL    NULL    NULL
Student1                        NULL   B      NULL   NULL    NULL    NULL
Student1                        NULL   NULL     C     NULL    NULL    NULL
Student2                        C       NULL  NULL   NULL    NULL    NULL
Student2                        NULL   NULL  NULL   A        NULL    NULL
Student3                        C        NULL  NULL   NULL    NULL    NULL
Student3                        NULL   NULL  A        NULL    NULL    NULL
Student3                        NULL   NULL  NULL   NULL    NULL    A
......................................................................................................
How can I flatten the structure of the table to have result looks like below:
All records per student will be dispalyed in one row
Students                       Class1 Class2 Class3 Class4 Class5 Class6
------------------------------ ------ ------ ----   ----- ------- -------
Student1                        A      B        C       NULL     NULL    NULL
Student2                        C      NULL   NULL   A         NULL    NULL
Student3                        C      NULL   A       NULL     NULL    A
...........................................................................................
TIA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ned Ten Centuries Points: 1214 More actions · Answer 1

Ned

Ten Centuries

Points: 1214

April 8, 2004 at 11:59 am

#502194

Can you post your DDL?

barsuk SSChampion Points: 12280 More actions · Answer 2

CREATE TABLE #TEMP

(

STUDENT VARCHAR(30) NOT NULL

,CLASS1 CHAR(1) NULL

,CLASS2 CHAR(1) NULL

,CLASS3 CHAR(1) NULL

,CLASS4 CHAR(1) NULL

,CLASS5 CHAR(1) NULL

,CLASS6 CHAR(1) NULL

)

INSERT #TEMP VALUES ('Student1','A',NULL ,NULL ,NULL ,NULL,NULL )

INSERT #TEMP VALUES ('Student1',NULL,'B' ,NULL ,NULL ,NULL,NULL )

INSERT #TEMP VALUES ('Student1',NULL,NULL ,NULL ,NULL ,NULL,'C' )

INSERT #TEMP VALUES ('Student2',NULL,'B',NULL ,NULL ,NULL,NULL )

INSERT #TEMP VALUES ('Student2',NULL,NULL,NULL ,NULL ,NULL,'D' )

INSERT #TEMP VALUES ('Student3',NULL,NULL ,NULL ,NULL ,NULL,'C' )

INSERT #TEMP VALUES ('Student3',NULL,'B',NULL ,NULL ,NULL,NULL )

INSERT #TEMP VALUES ('Student3',NULL,NULL,'A' ,NULL ,NULL,NULL )

INSERT #TEMP VALUES ('Student3','A',NULL ,NULL ,NULL ,NULL,NULL )

Select * from #TEMP

noeld SSC Guru Points: 96590 More actions · Answer 3

Select STUDENT

, Min(CLASS1) as Cls1

, Min(CLASS2) as Cls2

, Min(CLASS3) as Cls3

, Min(CLASS4) as Cls4

, Min(CLASS5) as Cls5

, Min(CLASS6) as Cls6

From #TEMP

Group by STUDENT

I would re-check the design though

* Noel