Row to column, column change is the problem we often encounter in the development process. Row-columns are typically implemented by the case-when statement, or by SQL SERVER 2005 's new operator pivot. In the traditional way, better understanding. The level is clear, and more accustomed. But pivot, UNPIVOT provides more syntax than a series of complex select ... The syntax specified in the case statement is simpler and more readable. Here are a few simple examples to introduce the column-change, row-and-column issues.
Let's start with a cliché example, a student's score sheet (simplified below) to figure out the downward row
CREATE TABLE [studentscores]
(
[UserName] NVARCHAR, --Student name
[Subject] NVARCHAR ( --Subject
[Score] FLOAT, --score
) INSERT into
[studentscores] SELECT ' Nick ', ' language ', 80
INSERT into [studentscores] SELECT ' Nick ', ' math ',
insert INTO [studentscores] select ' Nick ', ' English ',
insert INTO [studentscores] select ' Nick ', ' creature ',
insert I NTO [studentscores] Select ' Kent ', ' language ',
insert INTO [studentscores] select ' Kent ', ' Math ', and
insert into [Stu Dentscores] Select ' Kent ', ' English ',
INSERT into [studentscores] select ' Kent ', ' creature ', 85
If I want to know each student's grade, and each student's score line, so that I see, statistics, export data
SELECT
UserName,
Max (case Subject when ' language ' THEN Score ELSE 0 end) as ' language ',
max (case Subject when ' math ' THEN Sc Ore ELSE 0 end) as ' math ',
max (case Subject when ' English ' THEN Score ELSE 0 end) as ' English ',
max (case Subject ' creature ') N Score ELSE 0 end) as ' creature ' from
dbo.[ Studentscores]
GROUP by UserName
The results of the query are shown in the figure, so that we can get a clear picture of each student's performance.