SQL query CASE: Row-to-column conversion using CASE WHEN]

Document directory

• Change row and column -- use CASE WHEN
•  
•  
• Change row and column-based installation -- use flock (SQL Server 2005 and later versions are available)

Changing rows and columns -- use case when to test tables and test data

Create table TestRowCol (

Name VARCHAR (10 ),

Place VARCHAR (10 ),

Valuw INT

);

 

 

Insert into TestRowCol VALUES ('zhang san', 'Dong ', 1 );

Insert into TestRowCol VALUES ('zhang san', 'south', 2 );

Insert into TestRowCol VALUES ('zhang san', 'west', 3 );

Insert into TestRowCol VALUES ('zhang san', 'bei', 4 );

Insert into TestRowCol VALUES ('Li si', 'Dong ', 5 );

Insert into TestRowCol VALUES ('lily', 'south', 6 );

Insert into TestRowCol VALUES ('lily', 'west', 7 );

Insert into TestRowCol VALUES ('lily', 'North', 8 );

 

Requirements

Set the title

Name place valuw

-------------------------------

 

Change

 

Name east south west north

 

 

Ideas

Case when + group by processing

 

Implementation

 

SELECT

Name,

SUM (case when place = 'east' THEN valuw ELSE '0' END) AS East,

SUM (case when place = 'south' THEN valuw ELSE '0' END) AS South,

SUM (case when place = 'west' THEN valuw ELSE '0' END) AS West,

SUM (case when place = 'north' THEN valuw ELSE '0' END) AS North

FROM

TestRowCol

GROUP

Name

 

Execution result

Name east south west north

------------------------------------------------------

Li Si 5 6 7 8

Zhang San 1 2 3 4

 

 

Row-and-column Update-use external (available for SQL Server 2005 and later) to test tables and test data

In-row column replacement -- use CASE WHEN

Implementation

SELECT

Name,

Tmp. [East] East,

Tmp. [South] South,

Tmp. [West] West,

Tmp. [North] North

FROM

TestRowCol

Partition (

SUM (valuw)

FOR place IN ([East], [South], [West], [North])

) Tmp

ORDER

Name