- Row and column conversions:
- Name Course Score
- Zhang San language 74
- Zhang San Mathematics 83
- Zhang San Physics 93
- John Doe Language 74
- John Doe Mathematics 84
- John Doe Physics 94
- Want to become (get the following result):
- Name Chinese mathematics Physics
- ---- ---- ---- ----
- Lee 474 84 94
- Sheet 374 83 93
- Create Table SC (name varchar (10), Course varchar (10), fractional float)
- INSERT INTO SC
- Select ' Zhang San ',' language ',
- Union
- Select ' Zhang San ',' math ',
- Union
- Select ' Zhang San ',' physics ',
- Union
- Select ' John Doe ',' language ',
- Union
- Select ' John Doe ',' math ',
- Union
- Select ' John Doe ',' physical ', 94
- Method 1:
- DECLARE @sql varchar (max)
- Set @sql =' SELECT '
- Select @[email protected]+', max (case when course = ' +course + ' thenfraction Else ' "end) [' + Course +'] ' From (Select Distinct course from SC) T
- Set @sql = STUFF (@sql, 8,1,")
- Print @sql
- Set @[email protected]+', name from SC Group by name '
- EXEC (@sql)
- Method 2:
- Select name, Math, physics, language from SC pivot ( max (score) for course in (Math, Physics, language)) T
- Method 3:
- DECLARE @sql varchar (8000)
- Select @sql = isnull (@sql + '],[', ') + courses from the SC GROUP by course
- Print @sql
- Set @sql = ' [' + @sql + '] '
- EXEC (' select * from "(SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) b ')
Row and column substitution:
- Name Chinese mathematics Physics
- ----------------------------------
- Sheet 380 90 85
- Lee 485 92 82
- Requires the use of the T-SQL language to achieve the following results:
- Course Dick and Harry
- ----------------------
- Language 80 85
- Mathematics 90 92
- Physics 85 82
- Drop Table SC
- Create Table SC (name varchar (10), language int, math int, physical int)
- INSERT INTO SC
- Select ' Zhang San ', 80,90,85
- Union All
- Select ' John Doe ', 85,92,82
- SELECT * FROM SC
- -------------This process is not unpivot, there is time to add?
- SELECT * into SC1 from (
- Select name,' language ' course, language score from SC
- Union
- Select name,' math ' course, math from SC
- Union
- Select name,' Physics ' course, physical from SC
- ) T
- Complementary unpivot, same effect as above
- Select Name, course, score into #sc1 from SC Unpivot (score for course in ([Language],[Mathematics],[Physics]) a
- DECLARE @sql varchar (8000)
- Set @sql =' SELECT '
- Select @[email protected]+', max (case when name = "+ name +" thenfraction Else ""End) [' + name +'] ' From (select distinct name from
- SC1) T
- Set @sql =stuff (@sql, 8,1,")
- Set @[email protected]+', course from the SC1 Group by course '
- Print @sql
- EXEC (@sql)
- Supplemental Dynamic pivot and Unpiot
- --------------Pivot
- DECLARE @sql varchar (8000)
- Select @sql = isnull (@sql + '],[', ') + courses from the TB GROUP by course
- Print @sql
- Set @sql = ' [' + @sql + '] '
- EXEC (' select * from "(SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) b ')
- --------------Unpivot
- DECLARE @sql varchar (8000)
- Select @sql = isnull (@sql + '],[', ") + name from syscolumns where id=object_id (' TB ') and colorder>1
- Set @sql = ' [' + @sql + '] '
- EXEC (' SELECT ' Name, course, score from (SELECT * from TBTB) a Unpivot (score for Course in (' + @sql + ')) b ')
SQL Server row and column conversions