【www.bbyears.com--php与数据库】
1、列转行
数据经过计算加工后会直接生成前端图表需要的数据源，但是程序里又需要把该数据经过列转行写入中间表中，下次再查询该数据时直接从中间表查询数据。
1.1 列换行语法
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(
)
1.2 行转列案例
WITH T
AS
(
SELECT 1 as TeamId,'测试团队1' as Team,80 'MEN',20 'WOMEN'
UNION
SELECT 2 as TeamId,'测试团队2' as Team,30 'MEN',70 'WOMEN'
)
---列转行------------------------------------
SELECT TeamId,Team ,TYPE=ATTRIBUTE,CNT=VALUE
FROM T
UNPIVOT (
VALUE FOR ATTRIBUTE IN ([MEN],[WOMEN])
) AS UPV
2、 行转列
行转列主要是从中间表里查询数据，SQL SERVER2005以下的版本则可以使用聚合函数来完成。
2.1 行转列语法
table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(
)
2.2、使用PIVOT实现
WITH T
AS
(
SELECT 1 AS ID,'测试团队1' TEAM,'MEN' ITEM,80 CENT
UNION
SELECT 1 AS ID,'测试团队1' TEAM,'WOMEN' ITEM,20 CENT
UNION
SELECT 2 AS ID,'测试团队2' TEAM,'MEN' ITEM,30 CENT
UNION
SELECT 2 AS ID,'测试团队2' TEAM,'WOMEN' ITEM,70 CENT
)
SELECT * FROM T PIVOT (SUM(CENT) FOR ITEM IN ([MEN],[WOMEN])) A
2.3、使用聚合函数实现
WITH T
AS
(
SELECT 1 AS ID,'测试团队1' TEAM,'MEN' ITEM,80 CENT
UNION
SELECT 1 AS ID,'测试团队1' TEAM,'WOMEN' ITEM,20 CENT
UNION
SELECT 2 AS ID,'测试团队2' TEAM,'MEN' ITEM,30 CENT
UNION
SELECT 2 AS ID,'测试团队2' TEAM,'WOMEN' ITEM,70 CENT
)
SELECT ID,TEAM,
SUM(CASE WHEN ITEM='MEN' THEN CENT ELSE 0 END) 'MEN',
SUM(CASE WHEN ITEM='WOMEN' THEN CENT ELSE 0 END) 'WOMEN'
FROM T
GROUP BY ID,TEAM
sql server 行转列,列转行函数
-- 注:支持sqlserver2005 及以上版本
--drop table tb
--create table tb(name varchar(10),course varchar(10),score int,sex char(4))
insert into tb values('张三','语文',74,'男')
insert into tb values('张三','数学',83,'男')
insert into tb values('张三','物理',93,'男')
insert into tb values('张三','english',60,'女')
insert into tb values('李四','语文',74,'女')
insert into tb values('李四','数学',84,'女')
insert into tb values('李四','物理',94,'女')
SELECT * FROM tb
--行转列
SELECT * FROM (
SELECT * FROM TB PIVOT(sum(score) FOR course IN (语文,数学,物理,english)) a
) B
order by name desc
--列转行
SELECT * FROM
(
SELECT * FROM TB
PIVOT(MAX(score) FOR course IN (语文,数学,物理)) a
)
B UNPIVOT( score for course in([语文],[数学],[物理]) ) c
SQLSERVER行转列和列转行
1： 行转列
子查询，获取一定数据集结果
SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN
(SELECT TOP 10 objid FROM T_MyAttention tma GROUP BY objid ORDER BY count(1) DESC)
GROUP BY objid,action
下面用 行转列语法获取 最终结果
select *
from
(
SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN
(SELECT TOP 10 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
微软官方的图：
2： 列转行
怎么把一条记录拆分成几条记录？
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 attribute IN([a], [b], [c])
) AS UPV
--结果
/*
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
*/