First, the ranks conversion. 1, longitudinally converted to horizontal. Table: Test (Name Char (10), KM Char (10), CJ Int) Name KM CJ --------------------------- ------------------- Zhang San language 80 three mathematics 86 three English 75 Li Si language 78 Li Si Mathematics 85 Li Si English 78
Requires display in a horizontal format, namely: want to become
Name Language Mathematics English ---------------------------- Zhang San 80 86 75 Li Si 78 85 78
Create Table Test (Name Char (10), KM Char (10), CJ Int) GoInsert Test Values ('Zhang San', 'Language', 80) Insert Test Values ('Zhang San', 'Mathematics ", 86) Insert Test Values ('Zhang San', 'English', 75) INSERT TEST VALUES ('Li Si', 'Language', 78) Insert Test Values ('Li Si', 'Mathematics ", 85) Insert Test Values (' Li Four ',' English ', 78)
Method 1: SELECT Name = Name, Language = SUM (Case Km When 'Language' Then CJ Else 0 End), Mathematics = SUM (Case Km When 'Mathematics' Ten CJ Else 0 End), English = SUM (Case Km When' English 'TEN CJ ELSE 0 end) from test group by name analysis: This method is simple and easy to understand, but the column name is fixed, not flexible enough. Method 2: Declare @SQL VARCHAR (8000) Set @SQL = 'SELECT NAME'SELECT @SQL = @SQL ', SUM (Case KM WHEN '' KM '' '' TEN CJ End) [' KM '] 'From (Select Distinct Km from Test) as aselect @SQL = @ SQL ' from test group by name'exec (@SQL)
Drop Table Test
Analysis: This method structure is slightly complicated, but it is very versatile, and its advantages can be reflected when the column number is not fixed.
Second, the transverse conversion is vertical
There is a unknownname a c b dtom 1 2 3 4SUN 1 2 3 4 Requirements Name Km Valuetom A 1TOM C 2 Tom B 3TOM D 4Sun A 1Sun C 2Sun B 3Sun D 4test: Create Table Unknown (Name Char (4), A INT, C INT, B INT, D INSERT UNKNOWN (Name, A, C, B, D) SELECT 'A', 1, 2, 3, 4UNION All Select 'B', 5, 6, 7, 8Union All Select 'C', 9, 10, 11, 12Union All Select 'D', 13, 14, 15, 161.Select * from (SELECT NAME, 'A' AS ITEM, A As Value from Unknown Allselect Name, 'B' AS Item, B AS Value from UNOWNUNON ALLSELECT NAME, 'C' AS ITEM, C AS Value from UNOWNUNION AllSelect Name, 'D' AS ITEM, D AS VALUE from UNKNOWN TMPORDER BY NAME, ITEM
2. Sort by column Select * from (SELECT NAME, 'A' AS ITEM, A As Value from Unknown AllSelect Name, 'B' AS ITEM, B AS VALUE from Unknown AllSelect Name, 'C' AS ITEM, C AS Value from UNKNOWNUNION ALLSELECT NAME, 'D' AS ITEM, D AS VALUE from UNKNOWN) TMPORDER BY Name, Charindex (Item, 'ACBD') or Using System Table Select View1. * from (SELECT NAME, 'A' AS ITEM, A as value from unknownunion allselect name, 'b' as item, b as value from unknownunion allselect name, 'c' as item, c as value from unknownunion allselect name, 'd' as item, d as value from unknown) view1, syscolumns Swhere view1.Item * = S.Name and ID = Object_id ('unknown') Order by View1.name, S.Colid or Select A.Name, item = B.NAME, VALUE = (Case B.Name When 'A' THEN A WHEN 'B' THEN B WHEN 'C' TEN C ELSE D End) from unknown a, syscolumns b Where b. ID = Object_ID ('unknown') and b.name <> 'name' Order by a.name, B. Colid When the column is more than a time, you can do this declare @SQL varchar (8000) set @SQL = 'select a.name, item = B.NAME, VALUE = SUM (Case B.Name' SELECT @SQL = @SQL 'When '' ' Name ' '' Then ' Name from (SELECT DISTI nct name from syscolumns where id = object_id ( 'unknown') and name <> 'name') as aselect @sql = @ sql 'end) from unknown a, syscolumns b where id = object_id (' 'unknown' ') and b .name <> '' '' group by a.name, b.name, b.Colid ORDER BY A.NAME, B.COLID'SELECT @SQLEXEC (@sql) 2, matrix is transposed with Table UnknownNam A C B D ------------------------------------ Tom 1 2 3 4SUN 5 6 7 8MON 9 10 11 12das 13 14 15 16hi 17 18 19 20
Requirements Name col1 col2 col3 col4 col5 in portrait format -------------------------------------- ------ Nam Tom Sun Mon Das HORA 1 5 9 13 17C 2 6 10 14 18B 3 7 11 15 19D 4 8 12 16 20 Method 1: Use a cycle. Create Table Test (Nam VARCHAR (4), A INT, C INT, B INT, D INT) INSERT TEST (NAM, A, C, B, D) SELECT 'TOM', 1, 2, 3, 4UNION All Select ' Sun ', 5, 6, 7, 8UNION All Select' Mon ', 9, 10, 11, 12UNION All Select' DAS ', 13, 14, 15, 16UNION All Select' Hor ', 17, 18, 19, 20
create proc proc_sky_blue (@tablename varchar (200)) asbegin set nocount on declare @col nvarchar (256) declare @makesql nvarchar (4000) declare @insertsql nvarchar (4000) declare @caculatesql nvarchar (400) declare @count int declare @i int create table #tmp (colname nvarchar (20)) select @caculatesql = 'select @ count = count (1) from' @tablename exec sp_executesql @caculatesql, N '@ count int output', @ count output if @count> = 1024 Begin Raiserror ('"Table number is too much, I can't get', 16, 1) end else begin select @ i = 0 while @count> 0 begin select @ i = @ i 1 select @makesql = ' alter table #tmp add col ' convert (varchar (20), @ i) ' int 'exec (@makesql) select @ count = @ count-1 end declare my_cursor cursor for select name from syscolumns where id = object_id (@ tablename) order by colid open my_cursor fetch next from my_cursor into @col while @@ fetch_status = 0 begin select @makesql = 'select @ insertsql = @insertsql convert (varchar (4),' @ col ') ' ', '' from ' @ tablename select @insertsql = N'insert #tmp values ( '' ' @ col ' '', 'execute sp_executesql @ makesql, N' @ insertsql nvarchar (4000) output ', @ insertsql output select @insertsql = left (@insertsql , LEN (@insertsql) -1) ')' EXEC (@insertsql) fetch next from my_cursor @
COL END Close My_CURSOR DEALLOCATE MY_CURSOR SELECT * FROM #TMP SET NOCOUNT OFF EndEndexec Proc_sky_blue 'Test'
DROP TABLE TESTDROP PROC PROC_SKY_BLUE Method 2:
--Test Data
Create Table Test (Nam VARCHAR (4), A INT, C INT, B INT, D INT) INSERT TEST (NAM, A, C, B, D) SELECT 'TOM', 1, 2, 3, 4UNION All Select ' Sun ', 5, 6, 7, 8UNION All SELECT' MON ', 9, 10, 11, 12UNION All Select' DAS ', 13, 14, 15, 16UNION All Select' Hor ', 17, 18, 19, 20UNION ALL Select 'Jun', 9, 10, 11, 12UNION All Select 'Feb', 13, 14, 15, 16Union All Select 'Mar', 17, 18, 19, 20Union All Select 'Apr', 9, 10, 11, 12Union All Select 'May', 13, 14, 15, 16Union All Select 'Jun', 17, 18, 19, 20Union All Select 'Jul', 9, 10, 11, 12Union All Select 'Aug', 13, 14, 15, 16Union All Select 'Sep', 17, 18, 19, 20Union All Select 'Oct', 9, 10, 11, 12Union All Select 'Nov', 13, 14, 15, 16Union All Select 'DEC', 17, 18, 19, 20
- Query declare @ s1 varchar (8000), @ s3 varchar (8000), @ s4 varchar (8000), @ s5 varchar (8000), @ i varchar (10) SELECT @ S1 = ', @ S2 =', @ S3 = '', @ S5 = '', @ = '0'select @ S1 = @ S1 ', @ ' @ i ' varchar (800 ) ', @ S2 = @ S2 ', @ ' @ i ' = '' '', @ S3 = @ S3 'SELECT @' @ i '= @' @ i ' ', ['' NAM ''] = '' CAST ([' Name '] as varchar) from test ', @ S4 = @ S4 ', @ ' @ i ' = '' SELECT '' SUBSTRING (@ ' @ i ', 2,8000)', @ S5 = @ S5 ' ' 'union all' ' @' @ i, @ i = cast (@i as int) 1FROM syscolumns where object_id ('test') = ID and color <> 1select @ S1 = Substring (@ S1, 2, 8000), @ S2 = Substring (@ s2,2,8000), @ S4 = Substring (@ s4, 2,8000), @ S5 = Substring @ S5, 16, 8000) SELECT @ S1, @ S2, @ S3, @ S4, @ S5
Exec ('declare' @ S1 'SELECT' @ S2 @ S3 'SELECT' @ S4 'EXEC (' @ S5 ')') GO
- Delete Test Table DROP TABLE TEST
