SQL row to column classic example (reprint)

Summary of row-to-column issues

1, Row to column

---1, the simplest row to column
/*

Question: Suppose there is a student score table (TB) as follows:

Name Course Score
Zhang San language 74
Zhang San Mathematics 83
Zhang San Physics 93
John Doe Language 74
John Doe Mathematics 84
John Doe Physics 94

Want to become (get the following result):

Name Chinese mathematics Physics
Lee 474 84 94
Sheet 374 83 93
*/
--For testing

IF object_id (' [TB] ') is not NULL DROP TABLE [TB]
GO

CREATE table TB (name varchar (10), course varchar (10), fractional int)
INSERT into TB values (' Zhang San ', ' language ', 74)
INSERT into TB values (' Zhang San ', ' math ', 83)
INSERT into TB values (' Zhang San ', ' physical ', 93)
INSERT into TB values (' John Doe ', ' language ', 74)
INSERT into TB values (' John Doe ', ' math ', 84)
INSERT into TB values (' John Doe ', ' physical ', 94)
Go

--sql SERVER 2000 Dynamic SQL, refers to the course of more than language, mathematics, physics, this course. (hereinafter)

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 a
Set @sql = @sql + ' from TB GROUP by name '
EXEC (@sql)

--by dynamically building @sql, you get the following script

Select name as name,
Max (case course when ' language ' then score else 0 end) language,
Max (case course when ' math ' then fraction else 0 end) Math,
Max (case course when ' physical ' then fraction else 0 end) physical
From TB
Group BY name

--sql SERVER 2005 Dynamic SQL.

DECLARE @sql varchar (8000)
Select @sql = isnull (@sql + '],[', ') + courses from the TB group by course
Set @sql = ' [' + @sql + '] '
EXEC (' SELECT * from "(SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) B ')

--Get SQL SERVER 2005 static SQL.

SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (language, mathematics, physics)) b

--Query results

/*
Name Mathematical Physics language
---------- ----------- ----------- -----------
Lee 484 94 74
Sheet 383 93 74

(The number of rows affected is 2 rows)
*/

--2 Plus Total
/*
Question: On the basis of the above results, the average score, the total score, the following results are obtained:

The average total score of the mathematical physics of the name Chinese
---- ---- ---- ---- ------ ----
Lee 474 84 94 84.00 252
Sheet 374 83 93 83.33 250
*/

--sql SERVER 2000 Static SQL.

Select name and name,
Max (case course when ' language ' then score else 0 end) language,
Max (case course when ' math ' then fraction else 0 end) Math,
Max (case course when ' physical ' then fraction else 0 end) Physics,
Cast (avg (fractional *1.0) as decimal (18,2)) average,
SUM (Score) Total
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 a
Set @sql = @sql + ', CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total from TB group by name '
EXEC (@sql)

--sql SERVER 2005 Static SQL.

Select M.*, N. average, N. Total score from
(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (language, mathematics, physics)) b) m,
(select Name, CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total from TB Group by name) n
where M. Name = N. Name

--sql SERVER 2005 Dynamic SQL.

DECLARE @sql varchar (8000)
Select @sql = isnull (@sql + ', ', ') + courses from the TB group by course
EXEC (' select m.*, N. average, N. Total score from
(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) b) m,
(select Name, CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total 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, different data according to the serial number to the column, the method basically with 1

If object_id (' tb1 ') is not null drop table TB1
Go
CREATE table TB1-Data Sheet
(
Cpici varchar (TEN) is not NULL,
CNAME varchar (TEN) is not NULL,
cvalue int NULL
)

--Inserting 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)

--the need for self-enhancement in sqlserver2000

ALTER TABLE TB1 add ID int identity
Go
DECLARE @s varchar (8000)
Set @s= ' Select Cpici '
Select @[email protected]+ ', max (case is 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 @[email protected]+ ' 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

--2005 can be used Row_number

DECLARE @s varchar (8000)
Set @s= ' Select Cpici '
Select @[email protected]+ ', max (case is 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 @[email protected]+ ' from (select Rn=row_number () over (partition by Cpici ORDER by GETDATE ()), * from TB1
) T GROUP by Cpici '

EXEC (@s)

---results

/*
cpici      cvlue1       cvlue2      cvlue3      cvlue4
---------------------- --------------------------------
t501       31           33          5            NULL
t502       3            22          3            NULL
t503       53           44          50          
Warning: An aggregation or other SET operation eliminates null values.

(3 rows affected)

*/

--For 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 ' 13883633601 ', 10, ' Dining ' UNION ALL
Select ' 18689704236 ', 20, ' Logistics ' union ALL
Select ' 13883633601 ', 20, ' Logistics ' union ALL
Select ' 13883633601 ', 20, ' Auto ' UNION ALL
Select ' 18689704236 ', 20, ' medical ' union ALL
Select ' 18689704236 ', ' it ' union ALL
Select ' 18689704236 ', 20, ' Auto ' UNION ALL
Select ' 13883633601 ', 50, ' dining '
Go

DECLARE @sql varchar (8000)
Set @sql = ' Select phone number, sum (length of call) sum of calls '
Select @[email protected]+ ', max (case is 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 @[email protected]+ ' 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)

-Results

/*

(The number of rows affected is 8 rows)

Phone number call sum industry 1 Industry 2 Industry 3 Industry 4
--------------- ----------- ---------- ---------- ---------- ----------
13883633601 100 Dining Car Logistics
18689704236 It automotive Logistics medical

(The number of rows affected is 2 rows)

*/

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 ', ' logistics ', ' High school ', ' 1986-1-1 ' UNION ALL
Select ' B ', ' logistics ', ' Junior ', ' 1984-3-7 ' UNION ALL
Select ' C ', ' management ', ' undergraduate ', ' 1987-2-1 ' UNION ALL
Select ' D ', ' operations ', ' specialties ', ' 1976-2-1 ' UNION ALL
Select ' E ', ' operations ', ' specialties ', ' 1943-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 [Email protected]+ ', sum (case LTrim (' [E-mail protected]+ ') when ' "' +ltrim (' + @colm + ') + '" then 1 els E 0 End)
[' +ltrim (' + @colm + ') + '] ' from (select distinct ' [e-mail protected]+ ' from TB where ' [email protected]+ ' are NOT null) As a
Set @sql = @sql + ' from TB Group by department '
EXEC (@sql) '

EXEC (@sql)
GO

EXEC getgroupbycol N ' education
exec getgroupbycol N ' Birth date '
exec getgroupbycol N ' name '

/*

(the number of rows affected is 5 rows)

Department    undergraduate           Junior High School           High School           Specialist          
------------------------------------------------
Operations    0           0            0           2
Administration    1           0            0           0
Logistics    0            1           1            0

(The number of rows affected is 3 rows)

Department 1 1943 12:00AM 1 1976 12:00AM 7 1984 12:00AM 1 1986 12:00AM 1 1987 12:00AM
---- ------------------ ------------------ ------------------ ------------------ ------------------
Action 1 1 0 0 0
Management 0 0 0 0 1
Logistics 0 0 1 1 0

(The number of rows affected is 3 rows)

Department A B C D E
---- ----------- ----------- ----------- ----------- -----------
Action 0 0 0 1 1
Management 0 0 1 0 0
Logistics 1 1 0 0 0

(The number of rows affected is 3 rows)
*/

The following examples are available for reference

1. Common Multi-table joint

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, multi-table based on the time to calculate the serial number
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 related
Http://topic.csdn.net/u/20100626/00/83499112-43ae-4caa-a1fd-268cc5138da6.html?seed=415671352&r=66513615#r_ 66513615

4, according to the number of rows to go to column

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. Turn according to the sort size

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

6, grouping sort by serial number to turn

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

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/lxl743p/article/details/45146551

SQL row to column classic example (reprint)