求助，列转行比较排列问题

Small__Wolf 2012-07-11 02:07:34

Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'张XX' as '第一展员','100000.000000' as '回款金额','昌XX' as '第二展员','100000.000000' as '回款金额','赵XX' as '第三展员','50000' as '回款金额','' as '第四展员','' as '回款金额'
union all
Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','50000.000000' as '回款金额','张XX' as '第二展员','20000.000000' as '回款金额','昌XX' as '第三展员','10000' as '回款金额','' as '第四展员','' as '回款金额'
union all
Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'昌XX' as '第一展员','10000.000000' as '回款金额','赵XX' as '第二展员','50000.000000' as '回款金额','张XX' as '第三展员','90000' as '回款金额','' as '第四展员','' as '回款金额'
union all
Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','10000.000000' as '回款金额','昌XX' as '第二展员','10000.000000' as '回款金额','张XX' as '第三展员','180000' as '回款金额','' as '第四展员','' as '回款金额'


将上面的结果转换成下面的格式


Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'张XX' as '第一展员','390000.000000' as '回款金额','赵XX' as '第二展员','160000.000000' as '回款金额','昌XX' as '第三展员','130000.000000' as '回款金额','' as '第四展员','' as '回款金额'




...全文
139 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
Small__Wolf 2012-07-11
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 的回复:]



Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'张XX' as '第一展员','100000.000000' as '回款金额1','昌XX' as '第二展员','100000.000000' as '回款金额2','赵XX' as '第三展员','50000' as '回款金额3','' as '第四展员','' as '回款金额4'
union all
Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','50000.000000' as '回款金额','张XX' as '第二展员','20000.000000' as '回款金额','昌XX' as '第三展员','10000' as '回款金额','' as '第四展员','' as '回款金额'
union all
Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'昌XX' as '第一展员','10000.000000' as '回款金额','赵XX' as '第二展员','50000.000000' as '回款金额','张XX' as '第三展员','90000' as '回款金额','' as '第四展员','' as '回款金额'
union all
Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','10000.000000' as '回款金额','昌XX' as '第二展员','10000.000000' as '回款金额','张XX' as '第三展员','180000' as '回款金额','' as '第四展员','' as '回款金额'

union all
Select '方XX' as '客户名称','680000.000000' as '累计消费金额' ,'刘XX' as '第一展员','100000.000000' as '回款金额1','彭XX' as '第二展员','100000.000000' as '回款金额2','赵XX' as '第三展员','50000' as '回款金额3','' as '第四展员','' as '回款金额4'
union all
Select '方XX' as '客户名称','680000.000000' as '累计消费金额' ,'刘XX' as '第一展员','50000.000000' as '回款金额','彭XX' as '第二展员','20000.000000' as '回款金额','昌XX' as '第三展员','10000' as '回款金额','' as '第四展员','' as '回款金额'



如果有多个客户，有点问题，请按客户统计他对应的展员排名！
Small__Wolf 2012-07-11
  • 打赏
  • 举报
 回复
感谢 shmilywcd！
天-笑 2012-07-11
  • 打赏
  • 举报
 回复 


;with t as (

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'张XX' as '第一展员','100000.000000' as '回款金额1','昌XX' as '第二展员','100000.000000' as '回款金额2','赵XX' as '第三展员','50000' as '回款金额3','' as '第四展员','' as '回款金额4'

	union all

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','50000.000000' as '回款金额','张XX' as '第二展员','20000.000000' as '回款金额','昌XX' as '第三展员','10000' as '回款金额','' as '第四展员','' as '回款金额'

	union all

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'昌XX' as '第一展员','10000.000000' as '回款金额','赵XX' as '第二展员','50000.000000' as '回款金额','张XX' as '第三展员','90000' as '回款金额','' as '第四展员','' as '回款金额'

	union all

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','10000.000000' as '回款金额','昌XX' as '第二展员','10000.000000' as '回款金额','张XX' as '第三展员','180000' as '回款金额','' as '第四展员','' as '回款金额'

),t2 as (

	select [客户名称],[累计消费金额] ,[第一展员] as [展员],convert(float,[回款金额1]) as [回款金额] from t union all

	select [客户名称],[累计消费金额] ,[第二展员],[回款金额2] from t union all

	select [客户名称],[累计消费金额] ,[第三展员],[回款金额3] from t union all

	select [客户名称],[累计消费金额] ,[第四展员],[回款金额4] from t 

)

----drop table tmp

select * into tmp from t2 where [展员] is not null and len([展员])>=1

--select * from tmp

declare @s nvarchar(4000),

		@q int

set @s=''

;with t as (

	select top 10000 [展员],row_number() over(order by sum([回款金额]) )  as Seq from tmp  group by [展员] order by sum([回款金额]) desc 

)



Select     @s=@s+','+quotename(a.[展员])+'=sum(case when [展员]='+quotename(a.[展员],'''')+' then [回款金额] else 0 end)'

from tmp a left join t b on a.[展员] = b.[展员]

group by a.[展员],b.Seq order by  b.Seq desc 



exec('select [客户名称],max([累计消费金额]) as [累计消费金额] '+@s+' from tmp group by [客户名称]')

----------------------------------------------



客户名称 累计消费金额        张XX                    赵XX                    昌XX

---- ------------- ---------------------- ---------------------- ----------------------

梁XX  680000.000000 390000                 160000                 130000
天-笑 2012-07-11
  • 打赏
  • 举报
 回复 


;with t as (

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'张XX' as '第一展员','100000.000000' as '回款金额1','昌XX' as '第二展员','100000.000000' as '回款金额2','赵XX' as '第三展员','50000' as '回款金额3','' as '第四展员','' as '回款金额4'

	union all

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','50000.000000' as '回款金额','张XX' as '第二展员','20000.000000' as '回款金额','昌XX' as '第三展员','10000' as '回款金额','' as '第四展员','' as '回款金额'

	union all

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'昌XX' as '第一展员','10000.000000' as '回款金额','赵XX' as '第二展员','50000.000000' as '回款金额','张XX' as '第三展员','90000' as '回款金额','' as '第四展员','' as '回款金额'

	union all

	Select '梁XX' as '客户名称','680000.000000' as '累计消费金额' ,'赵XX' as '第一展员','10000.000000' as '回款金额','昌XX' as '第二展员','10000.000000' as '回款金额','张XX' as '第三展员','180000' as '回款金额','' as '第四展员','' as '回款金额'

),t2 as (

	select [客户名称],[第一展员] as [展员],convert(float,[回款金额1]) as [回款金额] from t union all

	select [客户名称],[第二展员],[回款金额2] from t union all

	select [客户名称],[第三展员],[回款金额3] from t union all

	select [客户名称],[第四展员],[回款金额4] from t 

)

----drop table tmp

select * into tmp from t2 where [展员] is not null and len([展员])>=1

--select * from tmp

declare @s nvarchar(4000),

		@q int

set @s=''

;with t as (

	select top 10000 [展员],row_number() over(order by sum([回款金额]) )  as Seq from tmp  group by [展员] order by sum([回款金额]) desc 

)



Select     @s=@s+','+quotename(a.[展员])+'=sum(case when [展员]='+quotename(a.[展员],'''')+' then [回款金额] else 0 end)'

from tmp a left join t b on a.[展员] = b.[展员]

group by a.[展员],b.Seq order by  b.Seq desc 



exec('select [客户名称],avg([回款金额]) as [回款金额] '+@s+' from tmp group by [客户名称]')

---------------------------------------

客户名称 回款金额                   张XX                    赵XX                    昌XX

---- ---------------------- ---------------------- ---------------------- ----------------------

梁XX  56666.6666666667       390000                 160000                 130000
Small__Wolf 2012-07-11
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
[/Quote]



我这个需要统计同一个人的回款金额总计，按照金额高低，从左到右依次排列，该如何弄？能否针对我上面的例子写一个函数，多谢！
追忆寻梦-转行互联网必备知识
课程由重庆追忆寻梦网络科技有限公司独家打造，都是互联网从业经验多年老司机录制而成。本课程主要讲解非互联网行业人员转行互联网的必备知识点，以及在转行过程中所遇到的问题及如何解决。
mysql交叉表查询列转行_SQL 转置计算
转置即旋转数据表的横纵方向，常用来改变数据布局，以便用新的角度观察。有些转置算法比较简单，比如行转列、列转行、双向转置；有些算法变化较多，比如动态转置、转置时跨行计算、关联转置等。这些转置算法对日常工作多有助益，值得我们学习讨论。基础转置行转列和列转行是最简单的转置算法，形式上互为逆运算，具体请看下面的问题及分析过程：1.行转列：将销量分组表的quater字段里的值(行)Q1-Q4，转为新字段名(...
offer求助 引流字节阿里百度腾讯网易米哈游华为比亚迪
主要做CV，多模态和AIGC算法，拿到了阿里达摩院，淘天集团，腾讯AI Lab，商汤研究院，华为中央研究院等多个公司的算法岗offer。九硕，株洲所11*15优点，福利待遇好，五险一金拉满，也会加班，节假日放假比较多，株洲房价很低，汽车事业部前景较好缺点，薪资到手不多，城市比较小，去了只能呆一辈。】在美团实习期间，我有一部分的工作内容是这样的，需要向多方。#晒一晒我的offer# #理想汽车# #秋招# 测评后三面，一面工程师技术短面，二面技术负责人技术面，三面部门经理业务面，目前发了意向书。
运维面试官喜欢问的问题
你最大的缺点是什么？ 行为问题（behavioural question），这类问题目的是看性格是否合适公司文化。主要考察：①你是否真心想做这个工作；②你性格与企业文化是否相符。所有答案都应该围绕这两点组织，即每个经历都应回归到你通过这个经历学到什么该职位所需关键技巧，这些经历为何让你想做这个工作，和该经历体现出你什么样的个人风格。 你最大的缺点是什么： ①避免避重就轻，谈一个算不得缺点的缺点，这...
求助？27岁零基础努力自学Python，多久或什么程度可以找到工作？
2015年1月原文： 背景： 毕业近5年，至今仍无任何表面成绩，一开始做期货投机（不算失败，不细说了），后经营个体店（失败，去年歇业），综合能力尚可，211大学管理专业（也许是史上最不专业的专业），世俗眼光压力、同类对比压力、年龄压力，久了不好受。 近况： 随便找个工作问题不大，但不甘于把时间浪费在“随便的工作”（原谅我的措辞。。因为没有专业技能只能从那样的工作做起），通过了解，选定IT方向为认命前最后努力方向，完全零基础自学没人带，刚启动2月有余，计划主要Python，次要前端H5/CSS3/JS，次次要
MS-SQL Server

34,576

社区成员

254,587

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章