在论坛中出现的比较难的sql问题:39(动态行转列 动态日期列问题)
来源:互联网 发布:数据挖掘工具 编辑:程序博客网 时间:2024/05/21 14:49
最近,在论坛中,遇到了不少比较难的sql问题,虽然自己都能解决,但发现过几天后,就记不起来了,也忘记解决的方法了。
所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。
求一SQL语句。
http://bbs.csdn.net/topics/390496661
create table #tab (col1 char(10),col2 char(10),item char(10),num int,[Date] varchar(10))insert #tab values('AAA','BBB','A',50,'2013-06-10')insert #tab values('ABB','BGG','B',30,'2013-06-10')insert #tab values('AAA','BBB','C',80,'2013-06-13')
我的解法:
create table tab (col1 char(10),col2 char(10),item char(10),num int,[Date] varchar(10))insert tab values('AAA','BBB','A',50,'2013-06-10')insert tab values('ABB','BGG','B',30,'2013-06-10')insert tab values('AAA','BBB','C',80,'2013-06-13')--动态生成sql语句declare @start_date varchar(10) = '2013-06-01', @end_date varchar(10) = '2013-06-30';declare @date varchar(10), @sql varchar(max) = '', @sql1 varchar(8000), @sql2 varchar(8000);set @date = @start_date;set @sql1 = 'select case when rownum = 1 then col1 else '''' end as col1, case when rownum = 1 then col2 else '''' end as col2, item'set @sql2 = 'select col1,col2,item,row_number() over(partition by col1,col2 order by item) as rownum' while @date <= @end_datebegin set @sql1 = @sql1 + ',v_' + REPLACE( right(@date,5),'-','') + ' as ''' + CAST(DATEPART(month,@date) as varchar) + '/' + CAST(DATEPART(day,@date) as varchar) +''''; set @sql2 = @sql2 + ',SUM(case when date =''' + @date + ''' then num else 0 end) as v_' + REPLACE( right(@date,5),'-','')set @date = CONVERT(varchar(10),dateadd(day,1,@date),120)endset @sql = @sql1 + ' from (' + @sql2 + ' from tab group by col1,col2,item' + ') v' --生产的动态sql语句 select @sqlexec(@sql)
上面由于是动态生成语句,所以不能用局部的临时表,所以建了一个表。
下面是动态生成的sql语句,经过了格式化:
select case when rownum = 1 then col1 else '' end as col1, case when rownum = 1 then col2 else '' end as col2, item, v_0601 as '6/1',v_0602 as '6/2',v_0603 as '6/3', v_0604 as '6/4',v_0605 as '6/5', v_0606 as '6/6',v_0607 as '6/7', v_0608 as '6/8',v_0609 as '6/9', v_0610 as '6/10',v_0611 as '6/11', v_0612 as '6/12',v_0613 as '6/13', v_0614 as '6/14',v_0615 as '6/15', v_0616 as '6/16',v_0617 as '6/17', v_0618 as '6/18',v_0619 as '6/19', v_0620 as '6/20',v_0621 as '6/21', v_0622 as '6/22',v_0623 as '6/23', v_0624 as '6/24',v_0625 as '6/25', v_0626 as '6/26',v_0627 as '6/27', v_0628 as '6/28',v_0629 as '6/29', v_0630 as '6/30' from (select col1,col2,item, row_number() over(partition by col1,col2 order by item) as rownum, SUM(case when date ='2013-06-01' then num else 0 end) as v_0601, SUM(case when date ='2013-06-02' then num else 0 end) as v_0602, SUM(case when date ='2013-06-03' then num else 0 end) as v_0603, SUM(case when date ='2013-06-04' then num else 0 end) as v_0604, SUM(case when date ='2013-06-05' then num else 0 end) as v_0605, SUM(case when date ='2013-06-06' then num else 0 end) as v_0606, SUM(case when date ='2013-06-07' then num else 0 end) as v_0607, SUM(case when date ='2013-06-08' then num else 0 end) as v_0608, SUM(case when date ='2013-06-09' then num else 0 end) as v_0609, SUM(case when date ='2013-06-10' then num else 0 end) as v_0610, SUM(case when date ='2013-06-11' then num else 0 end) as v_0611, SUM(case when date ='2013-06-12' then num else 0 end) as v_0612, SUM(case when date ='2013-06-13' then num else 0 end) as v_0613, SUM(case when date ='2013-06-14' then num else 0 end) as v_0614, SUM(case when date ='2013-06-15' then num else 0 end) as v_0615, SUM(case when date ='2013-06-16' then num else 0 end) as v_0616, SUM(case when date ='2013-06-17' then num else 0 end) as v_0617, SUM(case when date ='2013-06-18' then num else 0 end) as v_0618, SUM(case when date ='2013-06-19' then num else 0 end) as v_0619, SUM(case when date ='2013-06-20' then num else 0 end) as v_0620, SUM(case when date ='2013-06-21' then num else 0 end) as v_0621, SUM(case when date ='2013-06-22' then num else 0 end) as v_0622, SUM(case when date ='2013-06-23' then num else 0 end) as v_0623, SUM(case when date ='2013-06-24' then num else 0 end) as v_0624, SUM(case when date ='2013-06-25' then num else 0 end) as v_0625, SUM(case when date ='2013-06-26' then num else 0 end) as v_0626, SUM(case when date ='2013-06-27' then num else 0 end) as v_0627, SUM(case when date ='2013-06-28' then num else 0 end) as v_0628, SUM(case when date ='2013-06-29' then num else 0 end) as v_0629, SUM(case when date ='2013-06-30' then num else 0 end) as v_0630 from tab group by col1,col2,item) v
0 0
- 在论坛中出现的比较难的sql问题:39(动态行转列 动态日期列问题)
- 在论坛中出现的比较难的sql问题:6(动态行转列 考试科目、排名动态列问题)
- 在论坛中出现的比较难的sql问题:42(动态行转列 考勤时间动态列)
- 在论坛中出现的比较难的sql问题:37(动态行转列 某一行数据转为列名)
- 在论坛中出现的比较难的sql问题:15(生成动态删除列语句 分组内多行转为多列)
- 在论坛中出现的比较难的sql问题:36(动态行转列 解析json格式字符串)
- 在论坛中出现的比较难的sql问题:33(递归 连续日期问题 )
- 在论坛中出现的比较难的sql问题:26(动态行专列+合并字符串、补足行数)
- 在论坛中出现的比较难的sql问题:46(日期条件出现的奇怪问题)
- 在论坛中出现的比较难的sql问题:8(递归问题 树形结构分组)
- 在论坛中出现的比较难的sql问题:12(递归问题2 拆分字符串)
- 在论坛中出现的比较难的sql问题:23(随机填充问题)
- 在论坛中出现的比较难的sql问题:30(row_number函数 物料组合问题)
- 在论坛中出现的比较难的sql问题:34(递归 获取连续值问题)
- 在论坛中出现的比较难的sql问题:35(时间间隔计算问题)
- 在论坛中出现的比较难的sql问题:38(字符拆分 字符串检索问题)
- 在论坛中出现的比较难的sql问题:1(字符串分拆+行转列问题 SQL遍历截取字符串)
- 在论坛中出现的比较难的sql问题:2(row_number函数+子查询)
- java newFixedThreadPool 报错
- CodeForces 413C Jeopardy!
- 查看Oracle表空间大小
- DPM目标检测-1
- Fragment系列之Transactions.commit和Activity的状态
- 在论坛中出现的比较难的sql问题:39(动态行转列 动态日期列问题)
- android:contentInsetStart android:contentInsetEnd contentInsetStart paddingstart paddingEnd
- Block--两个界面 间回调传值
- 一些代码优化的方法
- Gitlab配置ssh连接,上传项目到Gitlab
- baselineAligned
- 测试手机视频录屏并转GIF功能
- 数据碎片解析
- 201,.plist文件 与 .pch文件的了解