sql server 行转列和列转行的使用

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

巴特西

sql server 行转列和列转行的使用

最新文章

热门文章