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
*/