求问怎么行转列查出来,最好是动态的,可能SQl里面还有聚合函数统计 sql行转列 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 请问结果中的Price字段值是怎么计算得来的? 你的结果中没看出行转成列啊,貌似就是一个简单的汇总,用sum就可以了 0 0,帮我看看,我写的SQL贴出来了 感觉写法太复杂了,不能灵活点么DECLARE @sql NVARCHAR(MAX)SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,' + N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 , ' from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'EXEC @sql用动态sql看看 DECLARE @sql NVARCHAR(MAX)SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,' + N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 ,' from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'EXEC @sql 你这个按PayChannel_Name分组统计即可,不需要动态行转列啊。如果需要动态行转列,可以参考:http://blog.csdn.net/dotnetstudio/article/details/9856745记得帮我顶下文章哈,谢谢 感觉写法太复杂了,不能灵活点么DECLARE @sql NVARCHAR(MAX)SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,' + N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 , ' from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'EXEC @sql用动态sql看看求问,可不可以写成视图DECLARE @sql NVARCHAR(MAX)SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.paychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付金额 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucess_User else 0 end) as ' + a.paychannel_name + N'订单成功人数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucesses else 0 end) as ' + a.paychannel_name + N'订单成功数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All_Users else 0 end) as ' + a.paychannel_name + N'总订单成功人数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All else 0 end) as ' + a.paychannel_name + N'总订单成功数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Fee else 0 end) as ' + a.paychannel_name + N'总消费阅点 ,'from (SELECT DISTINCT paychannel_name FROM [MID].UserRecharge) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from [MID].UserRecharge group by dateid'EXEC (@sql) 感觉写法太复杂了,不能灵活点么DECLARE @sql NVARCHAR(MAX)SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,' + N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 , ' from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'EXEC @sql用动态sql看看求问,可不可以写成视图DECLARE @sql NVARCHAR(MAX)SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.paychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付金额 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucess_User else 0 end) as ' + a.paychannel_name + N'订单成功人数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucesses else 0 end) as ' + a.paychannel_name + N'订单成功数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All_Users else 0 end) as ' + a.paychannel_name + N'总订单成功人数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All else 0 end) as ' + a.paychannel_name + N'总订单成功数 ,' + N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Fee else 0 end) as ' + a.paychannel_name + N'总消费阅点 ,'from (SELECT DISTINCT paychannel_name FROM [MID].UserRecharge) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from [MID].UserRecharge group by dateid'EXEC (@sql)一方面视图的结构创建的时候是固定的,而你的要求是可以动态,这个有点相冲突,听版主的用存储过程吧 自动生成行号 SQL 表格数据统计的存储过程 请教像这样的格式要怎么看? 那些数学符号代表什么意思? 求救!数据库的题目。在线等 @@rowcount死循环了... 如何将表中两条完全相同的记录合并成一条记录?急 紧急求援:SqlServer 2000企业版 未知错误:8007007F sql中有类似dos中的dir命令吗 为什么我的存储过程死循环啊? 循环平稳与a稳定分布 求一SQL语句 一个小型的数据库查询
SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,'
+ N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 , '
from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'
EXEC @sql用动态sql看看
SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,'
+ N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 ,'
from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'
EXEC @sql
http://blog.csdn.net/dotnetstudio/article/details/9856745记得帮我顶下文章哈,谢谢
SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,'
+ N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 , '
from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'
EXEC @sql用动态sql看看求问,可不可以写成视图
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.paychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付金额 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucess_User else 0 end) as ' + a.paychannel_name + N'订单成功人数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucesses else 0 end) as ' + a.paychannel_name + N'订单成功数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All_Users else 0 end) as ' + a.paychannel_name + N'总订单成功人数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All else 0 end) as ' + a.paychannel_name + N'总订单成功数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Fee else 0 end) as ' + a.paychannel_name + N'总消费阅点 ,'
from (SELECT DISTINCT paychannel_name FROM [MID].UserRecharge) AS a
SET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from [MID].UserRecharge group by dateid'
EXEC (@sql)
SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.pNaychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付 ,'
+ N' max(case when paychannel_name) = ''' + a.paychannel_name + ''' then userorder_sucess_user else 0 end) as ' + a.paychannel_name + N'成功人数 , '
from (SELECT DISTINCT paychannel_name FROM t1) AS aSET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from t1 group by dateid'
EXEC @sql用动态sql看看求问,可不可以写成视图
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql , '') + N' max(case when paychannel_name = ''' + a.paychannel_name + N''' then price else 0 end) as ' + a.paychannel_name + N'支付金额 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucess_User else 0 end) as ' + a.paychannel_name + N'订单成功人数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_Sucesses else 0 end) as ' + a.paychannel_name + N'订单成功数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All_Users else 0 end) as ' + a.paychannel_name + N'总订单成功人数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Userorder_All else 0 end) as ' + a.paychannel_name + N'总订单成功数 ,'
+ N' max(case when paychannel_name = ''' + a.paychannel_name + ''' then Fee else 0 end) as ' + a.paychannel_name + N'总消费阅点 ,'
from (SELECT DISTINCT paychannel_name FROM [MID].UserRecharge) AS a
SET @sql = N'select dateid , ' + LEFT(@sql , LEN(@sql) - 1) + N' from [MID].UserRecharge group by dateid'
EXEC (@sql)一方面视图的结构创建的时候是固定的,而你的要求是可以动态,这个有点相冲突,听版主的用存储过程吧