SQL row and column career change

Source: Internet
Author: User
Tags case statement

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

      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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.