SQL Server row and column conversions

1. Row and column conversions:

2. Name Course Score
4. Zhang San language 74
6. Zhang San Mathematics 83
8. Zhang San Physics 93
10. John Doe Language 74
12. John Doe Mathematics 84
14. John Doe Physics 94
18. Want to become (get the following result):
20. Name Chinese mathematics Physics
22. ---- ---- ---- ----
24. Lee 474 84 94
26. Sheet 374 83 93
30. Create Table SC (name varchar (10), Course varchar (10), fractional float)
34. INSERT INTO SC
36. Select ' Zhang San ',' language ',
38. Union
40. Select ' Zhang San ',' math ',
42. Union
44. Select ' Zhang San ',' physics ',
46. Union
48. Select ' John Doe ',' language ',
50. Union
52. Select ' John Doe ',' math ',
54. Union
56. Select ' John Doe ',' physical ', 94
60. Method 1:
62. DECLARE @sql varchar (max)
64. Set @sql =' SELECT '
66. Select @[email protected]+', max (case when course = ' +course + ' thenfraction Else ' "end) [' + Course +'] ' From (Select Distinct course from SC) T
70. Set @sql = STUFF (@sql, 8,1,")
72. Print @sql
74. Set @[email protected]+', name from SC Group by name '
76. EXEC (@sql)
80. Method 2:
84. Select name, Math, physics, language from SC pivot ( max (score) for course in (Math, Physics, language)) T
90. Method 3:
92. DECLARE @sql varchar (8000)
94. Select @sql = isnull (@sql + '],[', ') + courses from the SC GROUP by course
96. Print @sql
98. Set @sql = ' [' + @sql + '] '
100. EXEC (' select * from "(SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) b ')

Row and column substitution:

2. Name Chinese mathematics Physics
4. ----------------------------------
6. Sheet 380 90 85
8. Lee 485 92 82
12. Requires the use of the T-SQL language to achieve the following results:
14. Course Dick and Harry
16. ----------------------
18. Language 80 85
20. Mathematics 90 92
22. Physics 85 82
26. Drop Table SC
30. Create Table SC (name varchar (10), language int, math int, physical int)
34. INSERT INTO SC
36. Select ' Zhang San ', 80,90,85
38. Union All
40. Select ' John Doe ', 85,92,82
44. SELECT * FROM SC
48. -------------This process is not unpivot, there is time to add?
50. SELECT * into SC1 from (
52. Select name,' language ' course, language score from SC
54. Union
56. Select name,' math ' course, math from SC
58. Union
60. Select name,' Physics ' course, physical from SC
62. ) T

1. Complementary unpivot, same effect as above

1. Select Name, course, score into #sc1 from SC Unpivot (score for course in ([Language],[Mathematics],[Physics]) a

2. DECLARE @sql varchar (8000)
4. Set @sql =' SELECT '
6. Select @[email protected]+', max (case when name = "+ name +" thenfraction Else ""End) [' + name +'] ' From (select distinct name from
10. SC1) T
12. Set @sql =stuff (@sql, 8,1,")
14. Set @[email protected]+', course from the SC1 Group by course '
16. Print @sql
18. EXEC (@sql)

1. Supplemental Dynamic pivot and Unpiot

2. --------------Pivot
4. DECLARE @sql varchar (8000)
6. Select @sql = isnull (@sql + '],[', ') + courses from the TB GROUP by course
8. Print @sql
10. Set @sql = ' [' + @sql + '] '
12. EXEC (' select * from "(SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) b ')
18. --------------Unpivot
20. DECLARE @sql varchar (8000)
22. Select @sql = isnull (@sql + '],[', ") + name from syscolumns where id=object_id (' TB ') and colorder>1
24. Set @sql = ' [' + @sql + '] '
26. EXEC (' SELECT ' Name, course, score from (SELECT * from TBTB) a Unpivot (score for Course in (' + @sql + ')) b ')

SQL Server row and column conversions