Problems with row-to-column and column-changing in SQL

Problems with row-to-column and column-changing in SQL

This is a common problem and a test question.

1. Row-to-column issues

Simple example

CREATE TABLE#T (MON1INT, MON2INT, MON3INT)GOINSERT  into#TVALUES(1,2,3)GOSELECT *  from#T--row to column; (union ALL)SELECTMON1 from#TUNION  AllSELECTMON2 from#TUNION  AllSELECTMON3 from#TGO--The final optimizationSELECT *  from (SELECTMON1 from#TUNION  AllSELECTMON2 from#TUNION  AllSELECTMON3 from#T) T

A more complex example

Specific implementation code

 --Create a table  GO  CREATE TABLE#T1 (NAMENVARCHAR( -), ChineseNVARCHAR( -), MATHNVARCHAR( -), 中文版NVARCHAR( -)  )  --inserting analog data  SELECT *  from#T1GO  INSERT  into#T1VALUES('Zhang San', the, -, -)  INSERT  into#T1VALUES('John Doe', -, +,NULL)  INSERT  into#T1VALUES('Harry',NULL, -,NULL)  GO  SELECT *  from#T1--Specific code implementation  GO  SELECT *  from (  SELECTNAME as 'name','language'  as 'Subjects', Chinese as 'Achievements'  from#T1UNION  All  SELECTNAME as 'name','Mathematics'  as 'Subjects', MATH as 'Achievements'  from#T1UNION  All  SELECTNAME as 'name','English'  as 'Subjects', 中文版 as 'Achievements'  from#T1) TOrder  byT. Name

Workaround Two

--The static scheme of a career change: unpivot,sql2005 and later versions

  -- method Two; This method automatically removes values that  are null SELECT Name,obj,grade    from #T1    for inch  as Up

This method is also popular on the internet

Information_schema. The columns view gets the columns to construct the rows, and the XML processing is also used.

2 Row career

Simple example

CREATE TABLE#T0 (MON1INT)INSERT  into#T0VALUES(1)INSERT  into#T0VALUES(2)INSERT  into#T0VALUES(3)SELECT *  from#T0--method One is limited to the fact that the data is not duplicatedSELECT(SELECTMON1 from#T0WHEREMON1=1)'1',(SELECTMON1 from#T0WHEREMON1=2)'2',(SELECTMON1 from#T0WHEREMON1=3)'3' 

Problems with row-to-column and column-changing in SQL