SQL row to column column career PIVOT UNPIVOT

1. Basic Tables

2. Row to column, note the use of ISNULL function, in the overall statistics, ISNULL (-,0) is necessary to use

3. The list of career changes, the list of languages, mathematics, English, politics, the conversion to 2 columns, score scname These two columns, is a new column, can be named Score11 Scname22, casual

4. Test scripts

SELECT *  fromdbo. SC--inserting test DataINSERTdbo. SCSELECT NEWID(),'001','Jack1','Mathematics', theUNIONSELECT NEWID(),'002','Jack2','language', -UNIONSELECT NEWID(),'003','Jack3','English', theUNIONSELECT NEWID(),'004','Jack4','political', the--Row to columnSELECT *  fromdbo. Scpivot (SUM(score) forScnameinch(Chinese, maths, English, politics)) asP--Row CareerSELECT *  from (    --here is the row to column    SELECTSno,sname,ISNULLLanguage0) as 'language',ISNULLMathematical0) as 'Mathematics',ISNULLEnglish0) as 'English',ISNULLPolitical0) as 'political'  fromdbo. SC PIVOT (SUM(score) forScnameinch(Chinese, maths, English, politics)) asp) Tunpivot (score forScnameinch(Chinese, maths, English, politics)) as upWHEREScore!=0SELECT *  from (    --here is the row to column    SELECTSno,sname,ISNULLLanguage0) as 'language',ISNULLMathematical0) as 'Mathematics',ISNULLEnglish0) as 'English',ISNULLPolitical0) as 'political'  fromdbo. SC PIVOT (SUM(score) forScnameinch(Chinese, maths, English, politics)) asp) tunpivot (Score11 forSCName22inch(Chinese, maths, English, politics)) as upWHEREScore11!=0

SQL row to column column career PIVOT UNPIVOT