Normal row-column conversion (version 1.0) only provides static and dynamic writing for SQL server 2000, and version 2.0 adds SQL server 2005 writing.
Question: Suppose there is a student orders table (tb) as follows:
Name course score
Zhang San Language 74
James math 83
Zhang San physical 93
Li Si language 74
Li Si mathematics 84
Li Si physical 94
(The following result is displayed ):
Name, Chinese, Mathematics, Physics
----------------
Li Si 74 84 94
Zhang San 74 83 93
-------------------
*/
Create table tb (name varchar (10), course varchar (10), score int)
Insert into tb values ('zhang san', 'China', 74)
Insert into tb values ('zhang san', 'mat', 83)
Insert into tb values ('zhang san', 'Physical ', 93)
Insert into tb values ('Li si', 'China', 74)
Insert into tb values ('Li si', 'mat', 84)
Insert into tb values ('lily', 'Physical ', 94)
Go
-- SQL server 2000 static SQL indicates that the course only includes three courses: Chinese, mathematics, and physics. (Same as below)
Select name as name,
Max (case course when 'China' then score else 0 end) language,
Max (case course when 'mate' then score else 0 end) math,
Max (case course when 'physical 'then score else 0 end) Physical
From tb
Group by name
-- SQL server 2000 dynamic SQL refers to three courses, including Chinese, mathematics, and physics. (Same as below)
Declare @ SQL varchar (8000)
Set @ SQL = 'select name'
Select @ SQL = @ SQL + ', max (case course when''' + course + ''' then score else 0 end) [' + course + ']'
From (select distinct course from tb) as
Set @ SQL = @ SQL + 'from tb group by name'
Exec (@ SQL)
-- SQL server 2005 static SQL.
Select * from (select * from tb) a between (max (score) for course in (Chinese, Mathematics, Physics) B
-- SQL server 2005 dynamic SQL.
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + '], [', '') + course from tb group by course
Set @ SQL = '[' + @ SQL + ']'
Exec ('select * from (select * from tb) a round (max (score) for course in ('+ @ SQL +') B ')
---------------------------------
/*
Problem: Based on the above results, the average score and total score are added. The following result is obtained:
Name, Chinese, mathematics, and physics average score
--------------------------
Li Si 74 84 94 84.00 252
Zhang San 74 83 93 83.33 250
*/
-- SQL server 2000 static SQL.
Select name,
Max (case course when 'China' then score else 0 end) language,
Max (case course when 'mate' then score else 0 end) math,
Max (case course when 'physical 'then score else 0 end) physics,
Cast (avg (score * 1.0) as decimal () average score,
Sum (score) total score
From tb
Group by name
-- SQL server 2000 dynamic SQL.
Declare @ SQL varchar (8000)
Set @ SQL = 'select name'
Select @ SQL = @ SQL + ', max (case course when''' + course + ''' then score else 0 end) [' + course + ']'
From (select distinct course from tb) as
Set @ SQL = @ SQL + ', average score of cast (avg (score * 1.0) as decimal (), total sum (score) from tb group by name'
Exec (@ SQL)
-- SQL server 2005 static SQL.
Select m. *, n. Average score, n. Total score from
(Select * from tb) a between (max (score) for course in (Chinese, Mathematics, Physics) B) m,
(Select name, cast (avg (score * 1.0) as decimal () average score, sum (score) total score from tb group by name) n
Where m. Name = n. Name
-- SQL server 2005 dynamic SQL.
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + ',', '') + course from tb group by course
Exec ('select m. *, n. Average score, n. Total score from
(Select * from tb) a round (max (score) for course in ('+ @ SQL +') B) m,
(Select name, cast (avg (score * 1.0) as decimal () average score, sum (score) total score from tb group by name) n
Where m. Name = n. name ')
Drop table tb
------------------
------------------
/*
Question: If the two tables change each other: the table structure and data are:
Name, Chinese, Mathematics, Physics
Zhang San 74 83 93
Li Si 74 84 94
(The following result is displayed ):
Name course score
------------
Li Si language 74
Li Si mathematics 84
Li Si physical 94
Zhang San Language 74
James math 83
Zhang San physical 93
--------------
*/
Create table tb (name varchar (10), Chinese int, mathematical int, physical int)
Insert into tb values ('zhang san', 93)
Insert into tb values ('Lee 4', 94)
Go
-- SQL server 2000 static SQL.
Select * from
(
Select name, course = 'China', score = Chinese from tb
Union all
Select name, course = 'mat', score = mathematics from tb
Union all
Select name, course = 'physical ', score = physical from tb
) T
Order by name, case course when 'China' then 1 when' math 'then 2 when' then 3 end
-- SQL server 2000 dynamic SQL.
-- Call the dynamic ecosystem of the system table.
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + 'Union all', '') + 'select Name, [course] = '+ quotename (Name, ''') + ', [score] = '+ quotename (Name) + 'from tb'
From syscolumns
Where name! = N'name' and ID = object_id ('tb') -- table name tb, excluding other columns whose names are names
Order by colid asc
Exec (@ SQL + 'order by name ')
-- SQL server 2005 dynamic SQL.
Select name, course, score from tb unaligned (score for course in ([language], [mathematics], [physics]) t
-- SQL server 2005 dynamic SQL, same as SQL server 2000 dynamic SQL.
--------------------
/*
Problem: add an average score and the total score to the above result. The following result is obtained:
Name course score
----------------
Li Si language 74.00
Li Si, mathematics 84.00
Li Si physical 94.00
Li Si average score 84.00
Li Si's total score is 252.00
Zhang San Chinese 74.00
Zhang San, mathematics 83.00
Zhang San physical 93.00
Michael Jacob has an average score of 83.33.
Zhang San's total score is 250.00
------------------
*/
Select * from
(
Select name as name, course = 'China', score = Chinese from tb
Union all
Select name as name, course = 'mat', score = mathematics from tb
Union all
Select name as name, course = 'physical ', score = physical from tb
Union all
Select name as name, course = 'average', score = cast (Chinese + mathematics + physics) * 1.0/3 as decimal () from tb
Union all
Select name as name, course = 'Total', score = Chinese + mathematics + physics from tb
) T
Order by name, case course when 'China' then 1 when' math 'then 2 when' 'then 3 when' average score 'then 4 when' total score 'then 5 end
Drop table tb
--------------------------------------------------
Below is the dawugui reply
--------------------------------------------------
Create table A (id char (3), num1 int, num2 int, num3 int, num4 int)
Insert A select '001', 80, 90, 50, 60
Insert A select '002 ', 84, 70, 60, 82
Go
-- SQL2005 implementation method:
Select * from
Unregister
(Num for col in ([num1], [num2], [num3], [num4]) T2) tmp
-- SQL2000 implementation:
--- Call the dynamic ecosystem of system tables
Declare @ s nvarchar (4000)
Select @ s = isnull (@ s + 'Union all', '') + 'select ID, [num] = '+ quotename (Name, ''') + ', qty = '+ quotename (Name) +' from'
From syscolumns
Where Name! = N'id' and ID = object_id ('A') -- table name A, excluding other columns whose column name is ID
Order by colid asc
Exec (@ s + 'order by ID asc, [num] asc ')
-- Generated static statement
Select ID, [num] = 'num1', Qty = [num1] from A union all
Select ID, [num] = 'num2', Qty = [num2] from A union all
Select ID, [num] = 'num3', Qty = [num3] from A union all
Select ID, [num] = 'num4', Qty = [num4] from
Order by ID asc, [num] asc
/*
ID num Qty
-------------------
001 num1 80
001 num2 90
001 num3 50
001 num4 60
002 num1 84
002 num2 70
002 num3 60
002 num4 82
------------------------------
*/
-- Dynamic method:
Declare @ s nvarchar (4000)
Select @ s = isnull (@ s + 'Union all', '') + 'select ID, [num] = '+ quotename (Name) + 'from'
From syscolumns
Where Name! = N'id' and ID = object_id ('A ')
Order by colid asc
Exec (@ s + 'order by ID asc ')
-- The generated statement is as follows:
Select ID, [num] = [num1] from A union all
Select ID, [num] = [num2] from A union all
Select ID, [num] = [num3] from A union all
Select ID, [num] = [num4] from
Order by ID asc, [num] asc
/*
ID num
---------------
001 80
001 90
001 50
001 60
002 82
002 60
002 70
002 84
*/
--- Drop table
/*
Rotate table data 90 degrees (in Sanya, Hainan)
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
Go
-- 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)
Print @ 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
----------------------------------------------------------------------------
/* Fixed statement :*/
Select t1. *, t2.y, t3.z from
(Select a = 'B', x = B from test1 where a = 'X') t1,
(Select a = 'B', y = B from test1 where a = 'y') t2,
(Select a = 'B', z = B from test1 where a = 'Z') t3
Where t1.a = t2.a and t1.a = t2.a
Union all
Select t1. *, t2.y, t3.z from
(Select a = 'C', x = c from test1 where a = 'X') t1,
(Select a = 'C', y = c from test1 where a = 'y') t2,
(Select a = 'C', z = c from test1 where a = 'Z') t3
Where t1.a = t2.a and t1.a = t2.a
Union all
Select t1. *, t2.y, t3.z from
(Select a = 'D', x = d from test1 where a = 'X') t1,
(Select a = 'D', y = d from test1 where a = 'y') t2,
(Select a = 'D', z = d from test1 where a = 'Z') t3
Where t1.a = t2.a and t1.a = t2.a
Union all
Select t1. *, t2.y, t3.z from
(Select a = 'E', x = e from test1 where a = 'X') t1,
(Select a = 'E', y = e from test1 where a = 'y') t2,
(Select a = 'E', z = e from test1 where a = 'Z') t3
Where t1.a = t2.a and t1.a = t2.a
----------------------------------------------------------------------------
/*
Table tb with the following data:
Project type Performance Improvement
Blow cleaning class 200 10
Takeout 100 5
Total 300 15
Convert:
Total project categories
Performance: 200, 100, 300
Commission 10 5 15
*/
Create table tb
(
Project type varchar (10 ),
Performance int,
Commission int
)
Insert into tb (project type, performance, Commission) values ('Blow-playing class)
Insert into tb (project type, performance, Commission) values ('takeout ', 5)
Insert into tb (project type, performance, Commission) values ('Total', 15)
Go
Select project type, sum (blow) as blow, sum (take-out) as take-out, sum (total) as total from
(
Select project type = 'performance ',
Blow-cleaning class = case when project type = 'Blow-cleaning class' then performance else 0 end,
Takeout = case when project type = 'takeout 'then performance else 0 end,
Total = case when project type = 'Total' then performance else 0 end
From tb
Union all
Select project type = 'Commission ',
Blow-cleaning class = case when project type = 'Blow-washing class' then = else 0 end,
Takeout = case when item type = 'takeout 'then Commission else 0 end,
Total = case when project type = 'Total' then Commission else 0 end
From tb
) M
Group by project type
Order by project type desc
Drop table tb
/*
Total project categories
-----------------------------------------
Performance: 200, 100, 300
Commission 10 5 15
(The number of affected rows is 2)
*/
--------------------------------------------------------------------------
/*
The tb table in the database is as follows:
Monthly 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
Project month-month
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?
*/
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
/* -- General Stored Procedure for row and column swapping (Original: Batch creation): swaps the columns of a specified table according to the specified fields */
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 + '''''''',
@ S3 = @ s3 + 'select @ '+ @ I + mailto: % 20 = @ + @ I +' + '', ['' + ['+ @ fdname +
'] + ''] ='' + Cast ([' + name + '] as varchar) from [' + @ tbname + ']',
@ S4 = @ s4 + ', @' + @ I + '= ''select mailto: % 20% 20 + @ I,
@ S5 = @ s5 + '+ ''union all mailto: % 20% 20 + + @ 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
-- 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
Go
Exec p_zj 'test', 'month', 'project'
Drop table Test
Drop proc p_zj
/*
Project month-month
------------------------------------------------
Benefits 200 210 220 230
Salary 100 110 120 130
300 310 320 330
(The number of affected rows is 3)
*/
/*
Static Statement (SQL2005)
*/
-- 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
Go
-- Test statement
SELECT * FROM
(
SELECT evaluation month, month, amount FROM
(SELECT month, salary, benefits, bonus FROM Test) p
Unregister
(Amount FOR assessment month IN (salary, welfare, bonus) 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