Recently interviewed an interview problem, suddenly a bit confused, only to say the idea, and then Baidu a bit, sorted out the idea, so record down, convenient to learn later. (Please refer to the annex for the test questions)
Related Data tables:
1.Score table
2.[user] Table
The SQL statement is as follows:
--Method One: Static SQL
Copy Code code as follows:
SELECT * FROM
(SELECT uid,name, score,scorename from Score,[user] WHERE score.uid=[user].id) As SourceTable
PIVOT (AVG (Score) for Scorename in ([English], [math]) as a
--Method Two: Dynamic SQL
Copy Code code as follows:
DECLARE @s NVARCHAR (4000)
SELECT @s = ISNULL (@s + ', ', ') + QuoteName (scorename)
From (select distinct scorename from Score) as A---column name do not repeat
Declare @sql NVARCHAR (4000)
SET @sql = '
Select r.* from
(select Uid,name,scorename,score from Score,[user] where score.uid=[user].id) as T
Pivot
(
Max (T.score)
For T.scorename in (' +@s+ ')
) as R '
EXEC (@sql)
--Method Three: when the case
Copy Code code as follows:
Select
Row_number () over (the order by [User].id) as number,
UID as user number,
Name as name,
Max (case scorename when ' English ' then Score else 0 end) English,
Max (case scorename when ' math ' then Score else 0 end) Mathematics
From Score,[user] WHERE score.uid=[user].id
GROUP BY Uid,[user].id,name
