Row and column conversions

Now there is such a data table student

Name Object Score

A EN 89

A CH 78

A HO 99

b EN 34

b CH 88

B HO 66

.

.

.

.

Requires the following styles to be generated

Name EN CH HO

A 89 78 99

B 34 88 66

Method:

CREATE TABLE t (name varchar), Object VARCHAR (Ten), score INT)

INSERT T SELECT ' a ', ' EN ', 89

UNION all SELECT ' a ', ' CH ', 78

UNION all SELECT ' a ', ' HO ', 99

UNION all SELECT ' B ', ' EN ', 34

UNION all SELECT ' B ', ' CH ', 88

UNION all SELECT ' B ', ' HO ', 66

--object the number of different values that appear is limited then use

SELECT name,

En=max (case is object= ' EN ' then score ELSE 0 END),

Ch=max (case is object= ' CH ' then score ELSE 0 END),

Ho=max (case if object= ' HO ' then score ELSE 0 END)

From T

GROUP by name

--If the different values of object appear variable

DECLARE @sql VARCHAR (8000)

SET @sql = ' SELECT name '

SELECT @[email protected]+ ', [' +object+ ']=max (Case object when ' +object+ ' "then score ELSE 0 END) ' from T GROUP by objec T

EXEC (@sql + ' from-t GROUP by name ')

DROP TABLE T

Row and column conversions