[MSSQL] how to merge the highest score subjects

A netizen in the group threw out the following question yesterday:

Name Chinese Mathematical English
Zhang San 75 90 85
Li Si 80 85 85
Obtain the table and query the subjects with the highest name score.
Name score subject
James 90 mathematics
Li Si 85 math, English

My solution:

Create Table [DBO]. [chengji2] ([name] [nchar] (10) null, [score] [int] Null, [subject] nchar (100) null ,) on [primary] ------------------------------------- create function [DBO]. [getkemu] (@ name char (10) = NULL) returns varchar (8000) asbegin declare @ r varchar (8000) set @ r = ''select @ r = @ r + ',' + rtrim (cast (subject as varchar) from chengji2 where name = @ name return stuff (@ r, 1, 2, '') end ---------------------------------- insert into chengji2 (name, score, subject) Select B. * From (Select name, max (score) score from (Select name, language as score, 'China' subject from chengji Union allselect name, mathematics as score, 'mat' subject from chengji Union allselect name, English as score, 'English 'subject from chengji) tgroup by name) a inner join (Select name, max (score) score, from (Select name, as score, 'China' subject from chengji Union allselect name, as score, math from chengji Union allselect name, as score, English score, 'English 'subject from chengji) tgroup by name, subject) B on. name = B. name and. score = B. score ------------------------------------------------------------- select name, score, DBO. getkemu (name) as subject from chengji2 group by name, score

Netizen 1's solution:

Select * into # tbfrom (select 'zhang san' as name, 60 as language, 70 as mathematics, 80 as English unionselect 'Li si' as name, 90 as language, 70 as mathematics, 90 as English unionselect 'wangwu' as name, 80 as language, 80 as mathematics, 80 as English) A -------------- select name, max (score) as score, (select case when language = max (score) then' language, 'else' end + case when mathematics = max (score) then' mathematics, 'else' 'end + case when English = max (score) then' English 'else' 'end from # TB AB where AB. name =. name) as subject from (Select name, Chinese as score, 'China' subject from # tbunion allselect name, mathematical as score, 'mat' subject from # tbunion allselect name, english as score, 'English 'subject from # TB) a group by. name -------------------- drop table # TB

Solution of user 2:

Create Table # TMP (ID int primary key, [name] varchar (255), Chinese int, mathematical int, English INT); insert into # TMP values (1, 'zhang san ', 75, 90, 85); insert into # TMP values (2, 'lily', 80, 85, 85); withtree as (select [name], score, subject from # tmpunpivot (score for subject in (Chinese, mathematics, English) as unpvt), maxtree as (select * from tree T1 where score> = (select max (score) from Tree T2 where t1.name = t2.name) Select name, score, subject = stuff (select ', '+ subject from maxtree T1 where t1.name = t2.name for XML Path (''), 1, 1,'') from maxtree t2group by name, score drop table # TMP

Summary:

My approach is feasible if it is simply an interview solution.

Functions may be subject to syntax restrictions for different databases. Using a function causes low efficiency.

I gave a general and highly efficient solution. At the beginning, I also thought about it, but I was not proficient in case when.

Netizen 2 is the domestic version of my solution