Create DATABASE Inter_transformation
Go
CREATE TABLE Tb
(
Uname varchar (30),
Course varchar (30),
fraction int
)
Go
INSERT into TB values (' Zhang San ', ' language ', 74)
INSERT into TB values (' Zhang San ', ' math ', 83)
INSERT into TB values (' Zhang San ', ' physical ', 93)
INSERT into TB values (' John Doe ', ' language ', 74)
INSERT into TB values (' John Doe ', ' math ', 84)
INSERT into TB values (' John Doe ', ' physical ', 94)
INSERT into TB values (' Harry ', ' language ', null)
INSERT into TB values (' Harry ', ' math ', 84)
INSERT into TB values (' Harry ', ' physical ', 94)
Go
SELECT * FROM Tb
Go
Select Uname,
MAX (case Course "language" then fraction else 0 end) language,
MAX (case Course when ' math ' then fraction else 0 end) Math,
MAX (case Course when ' physical ' then fraction else 0 end) physical
From TB
GROUP BY Uname
SQL Server row-to-column interoperability