SQL Server row-to-column, column-career [Goto]

To create a table:

CREATE TABLE [dbo]. [Tbl_student] (
    [ID] [int] IDENTITY (*) Not NULL,
    [Student ID] [varchar] (COLLATE) Chinese_prc_ci_as NULL,
    [Student name] [nvarchar] (COLLATE) Chinese_prc_ci_as NULL,
    [Course ID] [NCHAR] (Ten) COLLATE chinese_prc_ci_as NULL,
    [Course name] [nvarchar] (COLLATE) Chinese_prc_ci_as NULL,
    Results [INT] Null
    [Teacher ID] [varchar] (COLLATE) Chinese_prc_ci_as NULL,
    [Teacher's name] [nvarchar] (COLLATE) Chinese_prc_ci_as NULL
) on [PRIMARY]

Insert data:

INSERT into tbl_student values (
' S3 ', ' Harry ', ' K4 '        , ' politics ', ' a ', ' T4 ', ' Miss Zhao ';
INSERT into tbl_student values (
' S1 ', ' Zhang San ', ' K1 '        , ' math ', ' a ', ' T1 ', ' Mr. Zhang ';
INSERT into tbl_student values (
' S2 ', ' John Doe ', ' K3 '        , ' English ', and ' T3 ', ' Miss Li ';
INSERT into tbl_student values (
' S1 ', '  Zhang San ', ' K4 '        , ' politics ', and ' T4 ', ' Miss Zhao ';
INSERT into tbl_student values (
' S2 ', ' John Doe ', ' K4 '        , ' politics ', and ' T5 ', ' Zhou Teacher ';
INSERT into tbl_student values (
' S3 ', ' Harry ', ' K2 '        , ' Chinese ', ' a ', ' T2 ', ' Miss Wang ';
INSERT into tbl_student values (
' S3 ', ' Harry ', ' K1 '        , ' mathematics ', ', ' T1 ', ' Zhang ';
INSERT into tbl_student values (
' S1 ', ' Zhang San ', ' K2 '        , ' Chinese ', Bayi, ' T2 ', ' Miss Wang ';
INSERT into tbl_student values (
' S4 ', ' Zhao Liu ', ' K2 '        , ' Chinese ', ' I ', ' T1 ', ' Miss Wang ';
INSERT into tbl_student values (
' S1 ', ' Zhang San ', ' K3 '        , ' English ', Notoginseng, ' T3 ', ' Miss Li ');
Null

Line train

Method 1:

Select Student name, course ID, score, teacher ID, teacher name,

Max (case course name when ' language ' then score else 0 end) language,
Max (case course name when ' math ' then score else 0 end) Math,
Max (case course name when ' English ' then score else 0 end) English
From Tbl_student Group by student name, course ID, score, teacher ID, teacher name

Method 2:

SELECT *

A pivot (max (score) for Course name in (Chinese, maths, English, politics)) b

Results

List of career change:

To create a table:

CREATE TABLE [dbo]. [Tbl_ Row Test] (
    [UserID] [INT] Null
    [Userno] [INT] Null
    [A] [int] NULL,
    [B] [int] NULL,
    [C] [int] NULL
) on [PRIMARY]

Insert data:

UserID Userno. A B C
1 1 11) 22 33

Method 1:

SELECT  Userid,userno,ttype=attribute
From (SELECT * from Tbl_ row test) a
  UNPIVOT
  (
    Value for attribute in (A, b,c)
  ) as UPV

Results

SQL Server row-to-column, column-career [Goto]