SQLSERVER行转列和列转行.docx

1：行转列?子查询，获取一定数据集结果SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN(SELECT TOP 10 objid FROM T_MyAttentiontma 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_MyAttentiontma GROUP BY objid ORDER BY count(1) DESC)GROUP BY objid,action) tpivot ( sum(count) for t.action in ([1],[2],[3],[4])) as ourpivot???微软官方的图：???2：列转行?怎么把一条记录拆分成几条记录？User???? No.???????? A?????????? B??????????? C1??????? 1?????????? 21????????? 34?????????? 241??????? 2?????????? 42????????? 25?????????? 16RESULT:User???? No.??????? Type?????? Num1??????? 1????????? A????????? 211??????? 1????????? B????????? 341??????? 1????????? C????????? 241??????? 2????????? A????????? 421??????? 2????????? B????????? 251??????? 2????????? C????????? 16declare @t table(usserint ,no int ,a int,bint, c int)insert into @t select 1,1,21,34,24union all select 1,2,42,25,16SELECT usser,no,Type=attribute, Num=valueFROM @t? UNPIVOT? (??? value FOR attribute IN([a], [b], [c])? ) AS UPV?列转行备注value FOR attribute IN([a], [b], [c])这句话中，a,b,c是列的名字，但是列名不能出现在上句的select语句中。-结果/*usser?? no?????? Type????? num----???????? ---????? --------? --------1????????? ?1???????? a????????? 211?????????? 1?????????b??????????341?????????? 1?????????c????????? 241?????????? 2?????????a????????? 421?????????? 2?????????b??????????251?????????? 2?????????c?????????? 16*/?T-SQL语句中，PIVOT命令可以实现数据表的列转行，UNPIVOT则与其相反，实现数据的行转列。本文结合实例说明了这一过程，希望能对您有所帮助。AD：WOT2015 互联网运维与开发者大会 热销抢票一、使用PIVOT和UNPIVOT命令的SQL Server版本要求1.数据库的最低版本要求为SQL Server 2005 或更高。2.必须将数据库的兼容级别设置为90 或更高。3.查看我的数据库版本及兼容级别。如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建一个查询窗口输入：print @@version，运行之后在我的本机上得到：Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)Apr? 2 2010 15:53:02Copyright (c) Microsoft CorporationExpress Edition with Advanced Services on Windows NT 5.2 X86 (Build 3790: Service Pack 2)然后我们选择一个数据库然后右