SQL Server row column conversion implementation idea record

Time:2021-11-22

Recently, I met an interview question in the interview. I was a little confused and only said my ideas. Later, baidu searched and sorted out my ideas, so I recorded it for future study. (please refer to the attachment for the interview questions)

Relevant data sheets:

1. Score table
SQL Server row column conversion implementation idea record 
2. [user] table
SQL Server row column conversion implementation idea record 
The SQL statement is as follows:

–Method 1: static SQL

Copy codeThe code is 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], [mathematics]) as a

–Method 2: dynamic SQL

Copy codeThe code is as follows:
DECLARE @s NVARCHAR(4000)
SELECT @s = ISNULL(@s + ‘,’, ”) + QUOTENAME(ScoreName)
From (select distinct scorename from score) as a — do not duplicate column names

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 (‘[email protected]+’)
) as r’
EXEC( @sql)

–Method 3: case when

Copy codeThe code is as follows:
select
row_ Number () over (order by [user]. ID) as number,
Uid as user number,
Name as name,
Max (case scorename when ‘English’ then score else 0 end),
Max (case scorename when ‘Math’ then score else 0 end)
from Score,[User] WHERE Score.UID=[User].ID
group by UID,[User].ID,Name

Recommended Today

Issue 8: front end nine inspiration sharing

Issue 8: front end nine inspiration sharing 1、 Set the verification of GIT commit(husky:^5Configuration before version is different from here) In general, every time we executeGit commit – M ‘submit information’The verification of commit information and code style will be triggered. If the verification fails, the commit will fail. How are these verifications added to […]