必发88手机客户端 5

Partition分组使用和行列转变

SELECT * FROM TableName temp /*需要行转列数据源*/
PIVOT
( sum(ColumnName1) /*行转列后 列的值*/ FOR
temp.ColumnName2/*需要行转列的列*/ IN
([办件信息成功总数],[办件信息更新],[办件信息失败总数],[办件信息新增]/*列的值*/)
) AS T

行转列后,原来的某个列的值变做了列名,在这里就是原来Subject列的值“数学”,”语文”…做了列名,而我们需要做的另一个工作就是计算这些列的值(这里的“计算”其实就是PIVOT里面的聚合函数(sum,COUNT等))

) T WHERE T.num <= 3 ORDER BY subject

SELECT * FROM
(
SELECT subject,name,score,Dense_Rank() OVER(PARTITION BY subject ORDER
BY score DESC) AS num FROM score

必发88手机客户端 1

必发88手机客户端 2

) T WHERE T.num <= 3 ORDER BY subject

SELECT * FROM
(
SELECT subject,name,score,Rank() OVER(PARTITION BY subject ORDER BY
score DESC) AS num FROM score

必发88手机客户端 3

SELECT ‘总数’ AS [总次数],
[数学],[英语],[语文] 
from (SELECT Subject as [科目],[id]
from TestRows2Columns)as tbl PIVOT
( COUNT([id])for tbl.[科目] IN([数学],[英语],[语文]))as tbl

SELECT * FROM
(
SELECT subject,name,score,ROW_NUMBER() OVER(PARTITION BY subject ORDER
BY score DESC) AS num FROM score

转换后

 PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。

–3.取每个学科的前3名数据

语法:

 

select * from score

SELECT * FROM (
select ActionTargetType+actiontype as TypeResult, COUNT(RowGuid) as
Number from BanJianLogInfo group by ActionTargetType,actiontype
union
select ActionTargetType+OperateResult+’总数’ as TypeResult,count(*)
from BanJianLogInfo
group by ActionTargetType,OperateResult
union
select ActionTargetType+’总数’ as TypeResult,count(*) from
BanJianLogInfo group by ActionTargetType
)temp
PIVOT
( sum(Number) /*行转列后 列的值*/ FOR
temp.TypeResult/*需要行转列的列*/ IN
([办件信息成功总数],[办件信息更新],[办件信息失败总数],[办件信息新增]/*列的值*/)
) AS T
转换前

select UserName,
sum(CASE Subject WHEN ‘数学’ THEN Source ELSE 0 END)as 数学
,sum(CASE Subject WHEN ‘英语’ THEN Source ELSE 0 END) as 英语
,sum(CASE Subject WHEN ‘语文’ THEN Source ELSE 0 END) as 语文
from [TestRows2Columns]
GROUP BY UserName

) T WHERE T.num <= 3 ORDER BY subject

/*以下是我对4个排名函数的类比表格:         
               排名连续性   排名并列性         
RANK()        不一定连续    有并列         
DENSE_RANK()   连续         有并列         
ROW_NUMBER()   连续         无并列         
NTILE()        连续         有并列         
*/

–4行转列

SELECT name,ISNULL([数学],0) [数学],ISNULL([英语],0)
[英语],ISNULL([语文],0) [语文]
FROM score PIVOT (max(score) FOR subject IN ([数学],[英语],[语文])
) AS pt

SELECT name,ISNULL([数学],0) [数学],ISNULL([英语],0) [英语]
FROM score PIVOT (max(score) FOR subject IN ([数学],[英语]) ) AS
pt

SELECT Name AS 姓名 ,
     MAX(CASE Subject WHEN ‘数学’ THEN score ELSE 0 END) [数学] ,
     MAX(CASE Subject WHEN ‘英语’ THEN score ELSE 0 END) [英语] ,
     MAX(CASE Subject WHEN ‘语文’  THEN score ELSE 0 END) [语文]
   FROM score GROUP BY name

–5动态行转列
declare @sql varchar(8000)
–set @sql = ‘select Name as ‘ + ‘姓名’
set @sql=”
select @sql = @sql+ ‘ , max(case Subject when ”’ + Subject + ”’ then
score else 0 end) [‘ + Subject + ‘]’
from (select distinct Subject from score) as a
–set @sql = @sql + ‘ from score group by name’
print @sql
exec(@sql)

–6 动态Pivot
declare @sql varchar(500)
declare @sql2 varchar(500)
select @sql2=  isnull(@sql2 + ‘,’ , ”) + ‘isnull([‘+Subject+’],0) as
‘+Subject+” from score group by Subject
select @sql = isnull(@sql + ‘,’ , ”) + ‘[‘+Subject+’]’ from score
group by Subject
print @sql
print @sql2
exec (‘select Name,’+@sql2+’ from (
select Name,s.Score,Subject from score s
 
) a pivot (sum(Score) for Subject in (‘ + @sql + ‘)) b’)

   
SELECT name,sum(score) score,
(SELECT Subject+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS StuList,
  (SELECT cast(score as varchar(10))+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS StuList
FROM score A
GROUP BY name

 

–7 XML PATH
   select b.name,b.score,
   LEFT(b.StuList,LEN(b.StuList)-1) StuList,
   LEFT(b.ScoreList,LEN(b.ScoreList)-1) ScoreList
    from (
    SELECT name,sum(score) score,
(SELECT Subject+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS StuList,
  (SELECT cast(score as varchar(10))+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS ScoreList
FROM score A
GROUP BY name ) b  order by score desc
  

  

 测试数据

CREATE TABLE score
(
name NVARCHAR(20),
subject NVARCHAR(20),
score INT
)
–2.插入测试数据
INSERT INTO score(name,subject,score) VALUES(‘张三’,’语文’,98)
INSERT INTO score(name,subject,score) VALUES(‘张三’,’数学’,80)
INSERT INTO score(name,subject,score) VALUES(‘张三’,’英语’,90)
INSERT INTO score(name,subject,score) VALUES(‘李四’,’语文’,88)
INSERT INTO score(name,subject,score) VALUES(‘李四’,’数学’,86)
INSERT INTO score(name,subject,score) VALUES(‘李四’,’英语’,88)
INSERT INTO score(name,subject,score) VALUES(‘李明’,’语文’,60)
INSERT INTO score(name,subject,score) VALUES(‘李明’,’数学’,86)
INSERT INTO score(name,subject,score) VALUES(‘李明’,’英语’,88)
INSERT INTO score(name,subject,score) VALUES(‘林风’,’语文’,74)
INSERT INTO score(name,subject,score) VALUES(‘林风’,’数学’,99)
INSERT INTO score(name,subject,score) VALUES(‘林风’,’英语’,59)
INSERT INTO score(name,subject,score) VALUES(‘严明’,’英语’,96)


select subject from score   for xml path(”)
SELECT Subject+’,’  FROM score
    where name=’林风’
  FOR XML PATH(”)
 
   SELECT  cast(score as varchar)+’,’ FROM score
    where name=’林风’
  FOR XML PATH(”)
 
SELECT name ,sum(score),
(SELECT Subject+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS StuList,
 
 
  (SELECT cast(score as varchar(10))+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS StuList
 
 
必发88手机客户端,FROM score A
GROUP BY name

必发88手机客户端 4

 

INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source])
SELECT N’张三’,N’语文’,60 UNION ALL
SELECT N’李四’,N’数学’,70 UNION ALL
SELECT N’王五’,N’英语’,80 UNION ALL
SELECT N’王五’,N’数学’,75 UNION ALL
SELECT N’王五’,N’语文’,57 UNION ALL
SELECT N’李四’,N’语文’,80 UNION ALL
SELECT N’张三’,N’英语’,100
GO

 PIVOT求参加每项的次数

SELECT ‘总数’ AS [总次数], [数学],[英语],[语文] –(选择行转列后的结果集的列)这里可以用“*”表示选择所有列,也可以只选择某些列(也就是某些科目)
from (SELECT Subject as [科目],[id] from TestRows2Columns)as tbl
PIVOT 查询所有科目和对应id号
( COUNT([id])for tbl.[科目] IN([数学],[英语],[语文]))as
tbl将列科目里面的值
分别转换成列并计算每个列的数量




必发88手机客户端 5

 

PIVOT行转列

SELECT *
FROM( SELECT [UserName] ,
[Subject] ,
[Source]
FROM[TestRows2Columns]
) p PIVOT
( SUM([Source]) FOR [Subject] IN ( [数学],[英语],[语文] ) ) AS
pvt
ORDER BY pvt.[UserName];
GO