code technique help #1

Question

Tools: SQL Management Studio 2008

Environment:Windows Vista and SQL Server 2008

Problem: I would like to list the following data

Name of the student | French  | Italian   | Spanish | German | Chinese |  English | Russian

and  a score listed on each language category

Code:

create  Table Mocha ( ID  int, pn int, name NVARCHAR(30), Languages NVARCHAR(15), score_scale NVARCHAR(2),exam_id char(1) )

insert into Mocha values (12345655, 274679300, 'Will Smith', 'Spanish','+2','A')
insert into Mocha values (12345655, 274679300, 'Will Smith', 'Italian','+2','B')

insert into Mocha values (00001258, 764051400, 'Lady Gaga', 'French','2','H')	
insert into Mocha values (00001258, 764051400, 'Lady Gaga', 'Chinese','+1','L')	
insert into Mocha values (00001258, 764051400, 'Lady Gaga', 'German','2','M')

insert into Mocha values (12345655, 862493644, 'Michael Douglas','English','4','E')
insert into Mocha values (12345655, 862493644, 'Michael Douglas ', 'Spanish','+3','F')

insert into Mocha values (42345655, 764051400, 'Tom Cruise', 'French','3','H')	
insert into Mocha values (42345655, 764051400, 'Tom Cruise', 'Chinese','1','L')	
insert into Mocha values (42345655, 764051400, 'Tom Cruise', 'German','+1','M')

insert into Mocha values (72345655, 564050000, 'Salma Hayek', 'Spanish','4','X')	
insert into Mocha values (72345655, 564050000, 'Salma Hayek', 'English','+2','O')	

insert into Mocha values (82327655, 564050999, 'Aniyta Arthon', 'Russian','Y')


Select * from mocha

The expected result should be, for example,

Name | French  | Italian   | Spanish | German | Chinese |  English | Russian

Laddy |     2  |                              |    +1      |        3   |

and so forth

Answer 1

SELECT Name,[Spanish],[Italian],[French],[Chinese],[German],[English],[Russian]
FROM 
	(SELECT Name
	,Languages
	,score_scale
	FROM Mocha) AS Src
		PIVOT (MAX(score_scale) FOR Languages IN 
				([Spanish],[Italian],[French],[Chinese],[German],[English],[Russian])
				) AS Pv

---

Narsimha

Answer 2

 

insert into Mocha values (82327655, 564050999, 'Aniyta Arthon', 'Russian','Y')


Select * from mocha

It's because your last Insert statement doesn't have 'score_scale'.

---

Narsimha