SQL Server row and column Change example tutorial

1, the column to change careers

After the data is calculated and processed, the data source needed for the front-end chart is generated directly, but the program needs to write the data into the middle table, and query the data directly from the middle table the next time the data is queried.

1.1-Column line wrapping syntax

Table_source

Unpivot (

Value_column

For Pivot_column

In (<column_list>)

)


1.2 Row column Case

With T
As
(
SELECT 1 as Teamid, ' Test team 1 ' as team,80 ' MEN ', ' WOMEN '
UNION
SELECT 2 as Teamid, ' Test Team 2 ' as team,30 ' MEN ', ' WOMEN '
)

---column to change careers------------------------------------

SELECT Teamid,team, Type=attribute,cnt=value
From T
Unpivot (
VALUE for ATTRIBUTE in ([Men],[women])
) as UPV

2. Row-Turn column

Row-Columns query data primarily from the middle table, and the following versions of SQL SERVER2005 can be accomplished using aggregate functions.

2.1 Row column Syntax

Table_source

PIVOT (

Aggregate Functions (Value_column)

For Pivot_column

In (<column_list>)

)


2.2, the use of pivot implementation

With T
As
(
SELECT 1 as ID, ' Test team 1 ' teams, ' MEN ' item,80 CENT
UNION
SELECT 1 as ID, ' Test team 1 ' teams, ' WOMEN ' item,20 CENT
UNION
SELECT 2 as ID, ' Test team 2 ' teams, ' MEN ' item,30 CENT
UNION
SELECT 2 as ID, ' Test team 2 ' teams, ' WOMEN ' item,70 CENT
)

SELECT * from T PIVOT (SUM (CENT) to ITEM in ([Men],[women])) A

2.3, using the aggregate function to achieve

With T
As
(
SELECT 1 as ID, ' Test team 1 ' teams, ' MEN ' item,80 CENT
UNION
SELECT 1 as ID, ' Test team 1 ' teams, ' WOMEN ' item,20 CENT
UNION
SELECT 2 as ID, ' Test team 2 ' teams, ' MEN ' item,30 CENT
UNION
SELECT 2 as ID, ' Test team 2 ' teams, ' WOMEN ' item,70 CENT
)

SELECT Id,team,
SUM (case when item= ' MEN ' THEN CENT ELSE 0) ' MEN ',
SUM (case when item= ' WOMEN ' THEN CENT ELSE 0) ' WOMEN '
From T
GROUP by Id,team



SQL Server row column, column career change function

--Note: Support sqlserver2005 and above version
--drop Table TB
--create table TB (name varchar, course varchar (), score Int,sex char (4))

INSERT into TB values (' John ', ' language ', 74, ' male ')

INSERT into TB values (' John ', ' Math ', 83, ' male ')

INSERT into TB values (' John ', ' Physics ', 93, ' Male ')
INSERT into TB values (' John ', ' 中文版 ', 60, ' female ')

INSERT into TB values (' Dick ', ' language ', 74, ' female ')

INSERT into TB values (' Dick ', ' math ', 84, ' female ')

INSERT into TB values (' Dick ', ' physics ', 94, ' female ')

SELECT * from TB


--row column
SELECT * FROM (
SELECT * from TB PIVOT (SUM (score)-Course in (Chinese, maths, physics, 中文版)) a
) B
ORDER BY name Desc


--Column Careers
SELECT * FROM
(
SELECT * from TB
PIVOT (MAX (score) for course in (Chinese, maths, physics)) a
)
B Unpivot (Score for course in ([Language],[Mathematics],[Physics]) c


SQL Server row and column career change

1: Row to column

subquery, getting a certain dataset result

SELECT Objid,action,count (1) as [count] from T_myattention WHERE ObjID in
(SELECT top ObjID from T_myattention TMA GROUP by ObjID-ORDER by Count (1) DESC)
GROUP by Objid,action

The final result is obtained by using the row-column syntax below

SELECT *
From
(
SELECT Objid,action,count (1) as [count] from T_myattention WHERE ObjID in
(SELECT top ObjID from T_myattention TMA GROUP by ObjID-ORDER by Count (1) DESC)
GROUP by Objid,action
) T
Pivot (sum (count) for t.action in ([1],[2],[3],[4])) as Ourpivot

Microsoft's Official Chart:

2: Row Careers

How to split a record into several records?

User No. A B C
1 1 21 34 24
1 2 42 25 16

Result:

User No. Type Num
1 1 A 21
1 1 B 34
1 1 C 24
1 2 A 42
1 2 B 25
1 2 C 16

Declare @t table (usser int, no int, a int,b int, c int)
Insert INTO @t Select 1,1,21,34,24
UNION ALL Select 1,2,42,25,16

SELECT Usser,no,type=attribute, Num=value
From @t
Unpivot
(
Value for attributes in ([a], [b], [C])
) as UPV

--The result

/*

Usser no Type num
----         ---      --------  --------
1 1 A 21
1 1 B 34
1 1 C 24
1 2 A 42
1 2 B 25
1 2 C 16
*/