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