[SQLServer] database row and column swaps

I haven't written any questions about the database SQL during the interview. After studying the questions, I will record the results to facilitate future queries. Question 1: Structure of the table tbltest1: studentkemufenshustudent1 language 80student1 mathematics 90student1 English 85student2 language 85student2 mathematics 92 student

I haven't written any questions about the database SQL during the interview. After studying the questions, I will record the results to facilitate future queries. Question 1: Structure of the table tbltest1: student kemu fenshu student1 language 80 student1 mathematics 90 student1 English 85 student2 Chinese 85 student2 mathematics 92 student

I haven't written any questions about the database SQL during the interview. After studying the questions, I will record the results to facilitate future queries.

Question 1: Change the columns of table tbltest1

Table Structure:

Student kemu fenshu
Student1 language 80
Student1 mathematical 90
Student1 English 85
Student2 language 85
Student2 mathematics 92
Student2 English 82

To:
Student Chinese, mathematics, and English
Student1 80 90 85
Student2 85 92 82

SQL statement of SQL Server:

Declare @ SQL varchar (4000)
Set @ SQL = 'select student'
Select @ SQL = @ SQL + ', sum (case kemu when ''' + kemu + ''' then fenshu else 0 end) [' + kemu + ']'
From (select distinct kemu from tbltest1) as
Set @ SQL = @ SQL + 'from tbltest1 group by student'
Exec (@ SQL)

Or

Select student, sum (case kemu when 'chine' then fenshu else 0 end), sum (case kemu when 'mate' then fenshu else 0 end, sum (case kemu when 'then fenshu else 0 end) English from tbltest group by student

Note: I personally think the above is good. If one or two options are available, the following SQL statements can be used. If there are more options, the preceding SQL statements are much more convenient.

In 2005, there seems to be another function that can be used. Wait until the study is completed and then try again.

Question 2: Merge

Table Structure tbltest2:

Id strings
1 my
1 name
1 is

1 xudayu
2 hello
2 world

Convert:
Id strings
1 my name is xudayu
2 hello world

SQL statement of SQL Server:

-- Create a merged Function
Create function fliehebin (@ id int)
Returns varchar (5000)
As
Begin
Declare @ str varchar (5000)
Set @ str =''
Select @ str = @ str + cast (strings as varchar (50) + ''from tbltest2 where id = @ id
Set @ str = subString (@ str, 1, len (@ str ))
Return (@ str)
End
Go
-- Call the custom function to obtain the result.
Select distinct id, dbo. fliehebin (id) from tbltest2

========================================================== ==================================

The legend is as follows:

If exists (select * from sysobjects where xtype = 'U' and name = 'data2 ') begin Drop table data2 End create table [data2] ([Personnel id] [varchar] (10) COLLATE Chinese_PRC_CI_AS not null, [basic salary] [numeric] (18, 2) NULL, [bonus] [numeric] (18, 2) NULL, [total] [numeric] (19, 2) NULL, CONSTRAINT [PK_data2] primary key clustered ([employee ID]) ON [PRIMARY]) ON [PRIMARY] GO insert data2 select 'a1', 1.00, 11.00, 111.00 insert data2 select 'a2 ', 2.00, 22.00, 222.00 insert data2 select 'a100', 3.00, 33.00, 333.00 insert data2 select 'a100', 100.00, 100.00, 100.00 go drop PROCEDURE AVB_IniTable go/* author: nyb time: 2005/04/22 fixtime: aim: transpose rows and columns input: @ TableNane EXECUTE: EXECUTE AVB_IniTable 'data2 '*/Create PROCEDURE AVB_IniTable @ TableNane varchar (128) as declare @ string VARCHAR (8000) -- 1 create View if exists (select * from sysobjects where xtype = 'V' and name = 'v _ Temp ') begin Drop view V_Temp End SELECT @ string = 'create view V_Temp as select * from' + @ TableNane EXECUTE (@ string) if exists (select * from sysobjects where xtype = 'U' and name = 'zztemp ') Begin Drop table zzTemp End DECLARE @ ColumnName VARCHAR (200) DECLARE @ ColumnStr VARCHAR (5000) select @ ColumnStr = ''select @ ColumnStr = @ ColumnStr + quotename (rtrim (staff ID) + 'float NULL, 'From V_Temp print @ ColumnStr SET @ ColumnStr = left (@ ColumnStr, len (@ ColumnStr)-1) SELECT @ string = 'create TABLE zzTemp (column name varchar (50) NULL, '+ @ ColumnStr +') ON [PRIMARY] 'print @ string EXECUTE (@ string) -- 2 insert record DECLARE Column_cur scroll cursor for select name FROM syscolumns where id = object_id (@ TableNane) and name <> 'employee No. 'open Column_cur fetch first from Column_cur into @ ColumnName WHILE (@ fetch_status <>-1) BEGIN select @ ColumnStr = ''if @ ColumnName = 'basic payroll 'select @ ColumnStr = @ ColumnStr + ''' + convert (varchar (20), ISNULL (basic salary, 0) + ''', 'from V_Temp else if @ ColumnName =' 'select @ ColumnStr = @ ColumnStr + ''' + convert (varchar (20 ), ISNULL (bonus, 0) + ''', 'From V_Temp else if @ ColumnName = 'total 'select @ ColumnStr = @ ColumnStr + ''' + convert (varchar (20), ISNULL (total, 0 )) + ''', 'from V_Temp SET @ ColumnStr = left (@ ColumnStr, len (@ ColumnStr)-1) select @ string = 'insert into zzTemp values (''' + @ ColumnName + ''', '+ @ ColumnStr +') 'execute (@ string) fetch next from Column_cur into @ ColumnName end close Column_cur DEALLOCATE Column_cur go -- view the result select * from data2 select * from zzTemp

========================================================== ==================================

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/* -- a common row-and-column swap stored procedure will specify the table, transpose by specified fields -- create 2004.04 -- * // * -- use example -- Test Data create table (Category varchar (10), male decimal ), female decimal (38.0) insert Table select 'fiction ', 59.2, 18.9 union all select 'prose', 30.6, 16.2 union all select 'philosophical, 10.2/* -- require conversion results gender novels prose philosophy ----- male 38.0 18.9 16.2 female 59.2 30.6 10.2 (affected rows are 2 rows) -- */-- call the Stored Procedure exec p_zj 'table', 'category', 'Gender '-- delete the test drop table -- */create proc p_zj @ tbname sysname, -- Name of the table to be processed @ fdname sysname, -- as the converted column name @ new_fdname sysname = ''-- specify the column name as declare @ s1 varchar (8000) for the converted column ), @ 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 +' = @ '+ @ I +' + '', [''+ cast (['+ @ fdname +'] as varchar) +''] = ''' + replace (['+ name +'], ''', ''') + ''' 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 order by colid select @ s1 = substring (@ s1, 2,8000 ), @ s2 = substring (@ s2,), @ s4 = substring (@ s4,), @ s5 = substring (@ s5) exec ('desc' + @ s1 + 'select' + @ s2 + @ s3 + 'select' + @ s4 + 'exec ('+ @ s5 +') go

Oracle's: