[SQL Server] row-to-column conversion problem summary 1-row-to-column Conversion

Summary of Row-to-column conversion problems-1. Row-to-column conversion (various problems in the forum will be sorted out later)

--- 1. Simplest row-to-column Conversion
/*

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
*/
-- Testing
If object_id ('[TB]') is not null drop table [TB]
Go
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 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)
-- Create @ SQL dynamically and obtain the following script:
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 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 ')
-- Obtain SQL Server 2005 static SQL.
Select * from (select * from TB) A between (max (score) for course in (Chinese, Mathematics, Physics) B

-- Query Result
/*
Name, mathematics, physics, and Chinese
-------------------------------------------
Li Si 84 94 74
Zhang San 83 93 74

(The number of affected rows is 2)
*/

-- 2 plus total
/*
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 ')

Other instances

Http://topic.csdn.net/u/20100708/18/55df5a90-27a7-4452-a69a-27f735539a1f.html? Seed = 24842417 & R = 66831902 # r_66831902

-- 3. Convert different data into columns by serial number. The method is basically the same as 1

If object_id ('tb1 ') is not null drop table tb1
Go
Create Table tb1 -- data table
(
Cpici varchar (10) Not null,
Cname varchar (10) Not null,
Cvalue int null
)
-- Insert Test Data
Insert into tb1 values ('t501', 'x1', 31)
Insert into tb1 values ('t501', 'x1', 33)
Insert into tb1 values ('t501', 'x1', 5)

Insert into tb1 values ('t502', 'x1', 3)
Insert into tb1 values ('t502', 'x1', 22)
Insert into tb1 values ('t502', 'x1', 3)

Insert into tb1 values ('t503', 'x1', 53)
Insert into tb1 values ('t503', 'x1', 44)
Insert into tb1 values ('t503', 'x1', 50)
Insert into tb1 values ('t503', 'x1', 23)

-- Auto-increment is required in sqlserver2000.
Alter table tb1 add ID int identity
Go
Declare @ s varchar (8000)
Set @ s = 'select cpici'
Select @ s = @ s + ', max (case when Rn =' + ltrim (RN) + 'then cvalue end) as cvlue '+ ltrim (RN)
From (select distinct rn from (select Rn = (select count (1) From tb1 where cpici = T. cpici and ID <= T. ID) from tb1 t) A) T
Set @ s = @ s + 'from (select Rn = (select count (1) From tb1 where cpici = T. cpici and ID <= T. ID), * From tb1 t
) T group by cpici'

Exec (@ s)
Go
Alter table tb1 drop column ID

-- Row_number can be used for another 2005
Declare @ s varchar (8000)
Set @ s = 'select cpici'
Select @ s = @ s + ', max (case when Rn =' + ltrim (RN) + 'then cvalue end) as cvlue '+ ltrim (RN)
From (select distinct rn from (select Rn = row_number () over (partition by cpici order by getdate () from tb1) a) T
Set @ s = @ s + 'from (select Rn = row_number () over (partition by cpici order by getdate (), * From tb1
) T group by cpici'

Exec (@ s)

--- Result
/*
Cpici cvlue1 cvlue2 cvlue3 cvlue4
------------------------------------------------------
T501 31 33 5 null
T502 3 22 3 Null
T503 53 44 50 23
Warning null values are eliminated for aggregation or other set operations.

(3 rows affected)

*/

-- Testing
If object_id ('[TB]') is not null drop table [TB]
Go
Create Table Tb (phone number varchar (15), call duration int, industry varchar (10 ))
Insert TB
Select '123', 10, 'catering 'Union all
Select '123', 20, 'logistic 'Union all
Select '123', 20, 'logistic 'Union all
Select '123', 20, 'auto' Union all
Select '200', 20, 'health' Union all
Select '2013', 20, 'it' Union all
Select '123', 20, 'auto' Union all
Select '123', 50, 'catering'
Go

Declare @ SQL varchar (8000)
Set @ SQL = 'select phone number, sum (Call duration) Call sum'
Select @ SQL = @ SQL + ', max (case when rowid =' + ltrim (rowid) + 'then industry else ''' end) as [industry '+ ltrim (rowid) +']'
From (select distinct rowid from (select count (distinct industry) from TB where phone number = T. Phone number and industry <= T. Industry) rowid
From TB t) A) B
Set @ SQL = @ SQL + 'from (select *, (select count (distinct industry) from TB where phone number = T. Phone number and industry <= T. Industry) rowid
From TB t) T group by phone number'
Exec (@ SQL)

-- Result
/*

(The number of affected rows is 8)

Total telephone number calls Industry 1 Industry 2 industry 3 industry 4
------------------------------------------------------------------
13883633601 100 catering vehicle Logistics
18689704236 80 it Automotive Logistics medical

(The number of affected rows is 2)

*/

Another dynamic row to column:

Http://topic.csdn.net/u/20100612/10/4CFCB667-89FA-4985-90D5-B8A420A6FF12.html

If object_id ('[TB]') is not null drop table [TB]
Go
Create Table [TB] ([name] varchar (1), [Department] varchar (4), [education] varchar (4), [Date of Birth] datetime)
Insert [TB]
Select 'A', 'logistic', 'high school', '2017-1-1 'Union all
Select 'B', 'logistic', 'Junior high ', '2017-3-7' Union all
Select 'C', 'Management', 'undergraduate ', '2017-2-1' Union all
Select 'D', 'operation', 'specialization', '2017-2-1 'Union all
Select 'E', 'operation', 'specialization', '2017-2-1'
Go

Go
If object_id ('getgroupbycol ') is not null drop proc getgroupbycol
Go
Create procedure [DBO]. [getgroupbycol]
@ Colm nvarchar (100)
As
Declare @ SQL varchar (4000)

Set @ SQL ='
Declare @ SQL varchar (8000)
Set @ SQL = ''select Department''
Select @ SQL = @ SQL + '', sum (Case ltrim ('+ @ Colm +') When ''' + ltrim ('+ @ Colm + ') + ''' then 1 else 0 end)
[''+ Ltrim ('+ @ Colm + ') + '']'' from (select distinct '+ @ Colm + 'from TB where' + @ Colm +' is not null) as
Set @ SQL = @ SQL + ''from TB group by Department''
Exec (@ SQL )'

Exec (@ SQL)
Go

Exec getgroupbycol n' Education'
Exec getgroupbycol n 'date of birth'
Exec getgroupbycol n' name'

/*

(The number of affected rows is 5)

Department undergraduate junior high school
------------------------------------------------
Operation 0 0 0 2
Manage 1 0 0 0
Logistics 0 1 1 0

(The number of affected rows is 3)

Department 02 1 1943 am 02 1 1976 am 03 7 1984 am 01 1 1986 am 02 1 1987 AM
----------------------------------------------------------------------------------------------
Operation 1 1 0 0 0
Manage 0 0 0 0 1
Logistics 0 0 1 1 0

(The number of affected rows is 3)

Department a B c d e
-----------------------------------------------------------
Operation 0 0 0 1 1 1
Manage 0 0 1 0 0
Logistics 1 1 0 0 0

(The number of affected rows is 3)
*/

Examples for reference below

1. common multi-table join

Http://topic.csdn.net/u/20100623/00/077055eb-784d-4b27-8407-2c17adc06c60.html? Seed = 81934135 & R = 66426155 # r_66426155

Http://topic.csdn.net/u/20100622/19/9710803c-441b-45d0-b010-703a2633fe89.html? 47161

2. Calculate the serial number of multiple tables based on time
Http://topic.csdn.net/u/20100623/12/bbb0921b-0e1b-4435-8e85-959d87844954.html? Seed = 2145286087 & R = 66438763 # r_66438763
Http://topic.csdn.net/u/20100701/09/1684649b-b893-463b-8b40-7f4b894cd41e.html? Seed = 205688256 & R = 66630774 # r_66630774

3. Financial Problems
Http://topic.csdn.net/u/20100626/00/83499112-43ae-4caa-a1fd-268cc5138da6.html? Seed = 415671352 & R = 66513615 # r_66513615

4. Column Conversion Based on the number of rows

Http://topic.csdn.net/u/20100705/12/e325571b-c368-4174-859f-17ae708eca3d.html

Http://topic.csdn.net/u/20100706/09/c34728dc-6167-45df-b7cf-974612b9aa8b.html

Http://topic.csdn.net/u/20100706/16/f217deed-a2be-4950-b911-2624ac7a881a.html? 39445

5. convert data by sorting size

Http://topic.csdn.net/u/20100707/13/63f4a02e-ebc3-4c71-9380-d6b2ca0eb366.html? 39970

6. Group sorting by serial number

Http://topic.csdn.net/u/20100725/05/7f813114-c423-4759-97b8-b22e1e2e90d7.html? Seed = 471594449 & R = 67220945 # r_67220945