How to rotate a table)

From: http://community.csdn.net/Expert/TopicView3.asp? Id = 5729506 There is a table in the database as follows: Salary and benefits January 10 0 200 300 February 11 0 210 310 March 12 0 220 320 April 13 0 230 330 What I want to get is January 2-March 4 Salary 100 110 120 130 Benefits 200 210 220 230 300 310 320 330 That is to say, the rows and columns of the table are completely reversed, a bit like the rotating matrix. How can I implement it using SQL statements?

	Reply:Limpire (last night building)() Credit: 100	2007-8-27 2:58:38	Score: 20
?	If exists (select * From DBO. sysobjects Where id = object_id (n' [DBO]. [p_zj] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [p_zj] Go /* -- Common row-column swapping stored procedure: swaps the specified table by specified field -- Producer build 2004.04 -- -- Example -- Test Data Create Table (Category varchar (10), male decimal (20, 1), female decimal (20, 1 )) Insert table select 'fiction ', 38.0, 59.2 Union all select 'prose ", 18.9, 30.6 Union all select 'philosophical, 16.2, 10.2 -- The Conversion Result is required. /* Gender novels prose Philosophy ------------------- Male 38.0 18.9 16.2 Female 59.2 30.6 10.2 */ -- Call a stored procedure Exec p_zj 'table', 'category', 'Gender' -- Delete test Drop table */ Create proc p_zj @ Tbname sysname, -- Name of the table to be processed @ Fdname sysname, -- used as the conversion column name @ New_fdname sysname = ''-- specifies the column name for the converted Column As Declare @ S1 varchar (8000), @ S2 varchar (8000) , @ S3 varchar (8000), @ S4 varchar (8000), @ S5 varchar (8000) , @ I varchar (10) Select @ S1 = '', @ S2 ='', @ S3 = '', @ S4 ='', @ S5 = '', @ I = '0' Select @ S1 = @ S1 + ', @' + @ I + 'varchar (8000 )' , @ S2 = @ S2 + ', @' + @ I + '= ''' + case isnull (@ new_fdname, '') When ''then'' Else @ new_fdname + '= 'end + ''' + name + '''''''' --, @ S2 = @ S2 + ', @' + @ I + '= ''Gender = ''' + name + '''''''' , @ S3 = @ S3 +' Select @ '+ @ I +' = @ '+ @ I +' + '', [''+ ['+ @ fdname +'] +''] = ''+ Cast (['+ name +'] As varchar) from ['+ @ tbname +']' , @ S4 = @ S4 + ', @' + @ I + '= 'select' + @' + @ I , @ S5 = @ S5 + '+ ''union all'' + @' + @ I , @ I = cast (@ I as INT) + 1 From syscolumns Where object_id (@ tbname) = ID and name <> @ fdname Select @ S1 = substring (@ S1, 2,8000) , @ S2 = substring (@ S2, 2,8000) , @ S4 = substring (@ S4, 2,8000) , @ S5 = substring (@ S5, 16,8000) Exec ('desc' + @ S1 +' Select '+ @ S2 + @ S3 +' Select '+ @ S4 +' Exec ('+ @ S5 + ')') Go
Top

	Reply:Limpire (last night building)() Credit: 100	2007-8-27 3:03:52	Score: 0
?	-- Use the following stored procedure test: Create Table Test (month varchar (4), salary int, benefit int, bonus INT) Insert Test Select 'february 11', 100,200,300 Union all Select 'february 11', 110,210,310 Union all Select 'february 11', 120,220,320 Union all Select 'August 11', 130,230,330 Select * from test /* Monthly salary and benefits January 10 0200300 February 11 0210310 March 12 0220320 April 13 0230330 */ Exec p_zj 'test', 'month', 'month' /* Month Welfare 200210220230 Salary: 100110120130 300310320330 Prize */ Drop Table Test -- Drop proc p_zj
Top

	Reply:Zlp321002 (Lead Brother 888)() Credit: 104	2007-8-27 9:20:21	Score: 20
?	-- Test environment Create Table Test (month varchar (4), salary int, benefit int, bonus INT) Insert Test Select 'february 11', 100,200,300 Union all Select 'february 11', 110,210,310 Union all Select 'february 11', 120,220,320 Union all Select 'August 11', 130,230,330 -- Test statement Select * from ( Select assessment month, month, amount From (Select month, salary, benefits, bonus From test) P Unregister (Amount for assessment month in (Salary, benefits, bonuses) ) As unpvt ) T Bytes (Max (amount) For month in ([January], [February], [March], [April]) ) As PT -- Test Result /* Assessment month-month ------------------------------ Welfare 200210220230 Salary: 100110120130 300310320330 Prize */ -- Delete the environment Drop Table Test
Top

	Reply:Dawugui (handsome old turtle)() Credit: 100	2007-8-27 9:36:34	Score: 20
?	Rotate table data 90 degrees Run the following table: A B C D E ---------------------------------------------------------------- X 1 2 3 4 Y 5 6 7 8 Z 9 10 11 12 Convert to the following result: A X Y Z -------------------------------------------------- B 1 5 9 C 2 6 10 D 3 7 11 E 4 8 12 -- Generate Test Data Create Table test1 (A varchar (20), B INT, C int, d int, e INT) Insert into test1 select 'x', 1, 2, 3, 4 Insert into test1 select 'y', 5, 6, 7, 8 Insert into test1 select 'Z', 9, 10, 11, 12 -- Generate an intermediate data table Declare @ s varchar (8000) Set @ s = 'create table Test2 (A varchar (20 )' Select @ s = @ s + ',' + A + 'varchar (10)' from test1 Set @ s = @ s + ')' Exec (@ s) -- Use an intermediate table to convert rows and columns Declare @ name varchar (20) Declare t_cursor cursor Select name from syscolumns Where id = object_id ('test1') and colid> 1 order by colid Open t_cursor Fetch next from t_cursor into @ name While @ fetch_status = 0 Begin Exec ('select' + @ name + 'as t into test3 from test1 ') Set @ s = 'insert into Test2 select ''' + @ name + '''' Select @ s = @ s + ', ''' + rtrim (t) + ''' from test3 Exec (@ s) Exec ('drop table test3 ') Fetch next from t_cursor into @ name End Close t_cursor Deallocate t_cursor -- View the result of row/column swaps Select * From test1 Select * From Test2 -- Delete a table Drop table test1 Drop table Test2