34,576
社区成员
发帖
与我相关
我的任务
分享再见行列转换 高难度？？？？？？
表TB1
name riqi jine
张三 2008-1-1 100
张三 2008-2-1 100
张三 2008-3-1 100
张三 2008-4-1 100
张三 2008-5-1 100
张三 2008-6-1 100
李四 2008-1-10 200
李四 2008-2-10 200
李四 2008-3-10 200
李四 2008-4-10 200
查询想要的结果
张三 2008-1-1 100 2008-2-1 100 2008-3-1 100 2008-4-1 100 2008-5-1 100 2008-6-1 100
李四 2008-1-10 200 2008-2-10 200 2008-3-10 200 2008-4-10 200
表中同一姓名的数据行不确定
name riqi jine
张三 2008-1-1 100
张三 2008-2-1 100
张三 2008-3-1 100
张三 2008-4-1 100
张三 2008-5-1 100
张三 2008-6-1 100
李四 2008-1-10 200
李四 2008-2-10 200
李四 2008-3-10 200
李四 2008-4-10 200
查询想要的结果
张三 2008-1-1 100 2008-2-1 100 2008-3-1 100 2008-4-1 100 2008-5-1 100 2008-6-1 100
李四 2008-1-10 200 2008-2-10 200 2008-3-10 200 2008-4-10 200
表中同一姓名的数据行不确定
...全文
请发表友善的回复…
发表回复
gaoge11 2009-11-16
- 打赏
- 举报
SQL77
那个不是以月份区分 我要取的是 前20行 变为列
所以根据月份是不行的
那个不是以月份区分 我要取的是 前20行 变为列
所以根据月份是不行的
ChinaJiaBing 2009-11-16
- 打赏
- 举报
----------try
declare @tb table (name nvarchar(10),riqi datetime,jine int)
insert into @tb select '张三','2008-1-1',100
union all select '张三','2008-2-1',100
union all select '张三','2008-3-1',100
union all select '张三','2008-4-1',100
union all select '张三','2008-5-1',100
union all select '张三','2008-6-1',100
union all select '李四','2008-1-10',200
union all select '李四','2008-2-10',200
union all select '李四','2008-3-10',200
union all select '李四','2008-4-10',200
select name ,rj= convert(nvarchar(10),riqi,120)+' '+ltrim(jine),
序号=row_number()over(partition by name order by riqi)
into # from @tb
--select * from # a pivot (max(rj) for 序号 in ([1],[2],[3],[4],[5],[6],[7],[8],[9]))b
declare @str nvarchar(4000)
set @str = 'select * from # a pivot (max(rj) for 序号 in (' +
stuff((select ',['+ltrim(序号)+']' from # group by 序号 for xml path('')),1,1,'') +')) b'
set @str=@str +' order by name desc'
exec (@str)
go
drop table #
(10 行受影响)
name 1 2 3 4 5 6
---------- ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------
张三 2008-01-01 100 2008-02-01 100 2008-03-01 100 2008-04-01 100 2008-05-01 100 2008-06-01 100
李四 2008-01-10 200 2008-02-10 200 2008-03-10 200 2008-04-10 200 NULL NULL
(2 行受影响)
liherun 2009-11-16
- 打赏
- 举报
谢谢大家了
david0927cs2006 2009-11-16
- 打赏
- 举报
楼主研究一下
liangCK
(小梁 爱 兰儿 ❤)
的动态方法,然后自己调整一下,这样对自己才会有提高的,说到底这种方法核心就在于递归,楼主研究一下,不难的.
liangCK
(小梁 爱 兰儿 ❤)
的动态方法,然后自己调整一下,这样对自己才会有提高的,说到底这种方法核心就在于递归,楼主研究一下,不难的.
liherun 2009-11-16
- 打赏
- 举报
金额没有显示出来
liangCK 2009-11-16
- 打赏
- 举报
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-16 14:16:03
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (name varchar(4),riqi datetime,jine int)
INSERT INTO [tb]
SELECT '张三','2008-1-1',100 UNION ALL
SELECT '张三','2008-2-1',100 UNION ALL
SELECT '张三','2008-3-1',100 UNION ALL
SELECT '张三','2008-4-1',100 UNION ALL
SELECT '张三','2008-5-1',100 UNION ALL
SELECT '张三','2008-6-1',100 UNION ALL
SELECT '李四','2008-1-10',200 UNION ALL
SELECT '李四','2008-2-10',200 UNION ALL
SELECT '李四','2008-3-10',200 UNION ALL
SELECT '李四','2008-4-10',200
--SQL查询如下:
declare @i int;
set @i = (select top 1 cnt
from (select count(*) as cnt from tb group by name) as t
order by cnt desc);
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',max(case when rowid ='+rtrim(number)+'
then convert(varchar(10),riqi,120) else '''' end)'
+ ',max(case when rowid ='+rtrim(number)+'
then rtrim(jine) else '''' end)'
from master.dbo.spt_values
where type = 'p' and number < @i;
exec('select name'+@sql+'
from (select *,rowid=row_number() over(partition by name order by riqi)-1
from tb) as t
group by name');
drop table tb;
/*
name (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名) (无列名)
李四 2008-01-10 200 2008-02-10 200 2008-03-10 200 2008-04-10 200
张三 2008-01-01 100 2008-02-01 100 2008-03-01 100 2008-04-01 100 2008-05-01 100 2008-06-01 100
*/
-狙击手- 2009-11-16
- 打赏
- 举报
精华里好多呀
liangCK 2009-11-16
- 打赏
- 举报
declare @i int;
set @i = (select top 1 cnt
from (select count(*) as cnt from tb group by name) as t
order by cnt desc);
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',max(case when rowid ='+rtrim(number)+'
then convert(varchar(10),riqi,120) else '' end)'
+ ',max(case when rowid ='+rtrim(number)+'
then rtrim(jine) else '' end)'
from master.dbo.spt_values
where type = 'p' and number < @i;
exec('select name'+@sql+'
from (select *,rowid=row_number() over(partition by name order by riqi)-1
from tb) as t
group by name');liherun 2009-11-16
- 打赏
- 举报
李四 end),max(case when rowid =0 then rtrim(jine) else 2008-01-10 2008-02-10 2008-03-10 2008-04-10 end),max(case when rowid =5 then rtrim(jine) else
张三 end),max(case when rowid =0 then rtrim(jine) else 2008-01-01 2008-02-01 2008-03-01 2008-04-01 2008-05-01liherun 2009-11-16
- 打赏
- 举报
3楼的，你的结果
结果不对？三楼的大大
李四 NULL NULL 2008-01-10 00:00:00 200 2008-02-10 00:00:00 200 2008-03-10 00:00:00 200 2008-04-10 00:00:00 200 NULL NULL
张三 NULL NULL 2008-01-01 00:00:00 100 2008-02-01 00:00:00 100 2008-03-01 00:00:00 100 2008-04-01 00:00:00 100 2008-05-01 00:00:00 100
结果不对？三楼的大大
liangCK 2009-11-16
- 打赏
- 举报
declare @i int;
set @i = (select top 1 cnt
from (select count(*) as cnt from tb group by name) as t
order by cnt desc);
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',max(case when rowid ='+rtrim(number)+'
then convert(varchar(10),riqi,120) else '' end)'
+ ',max(case when rowid ='+rtrim(number)+'
then rtrim(jine) else '' end)'
from master.dbo.spt_values
where type = 'p' and number < @i;
exec('select name'+@sql+'
from (select *,rowid=row_number() over(partition by name order by riqi)
from tb) as t
group by name');
sdnwjd 2009-11-16
- 打赏
- 举报
回帖即可获取可用分
liherun 2009-11-16
- 打赏
- 举报
有NULL的，怎么能去掉？
--小F-- 2009-11-16
- 打赏
- 举报
用3楼P梁的动态
SQL77 2009-11-16
- 打赏
- 举报
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone
*/
-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(name VARCHAR(10),nameriqi smalldatetime,jine int)
Go
Insert into TB
SELECT'张三', '2008-1-1',100 union all
SELECT'张三', '2008-2-1',100 union all
SELECT'张三','2008-3-1',100 union all
SELECT'张三','2008-4-1',100 union all
SELECT'张三','2008-5-1',100 union all
SELECT'张三','2008-6-1',100 union all
SELECT'李四','2008-1-10',200 union all
SELECT'李四','2008-2-10',200 union all
SELECT'李四','2008-3-10',200 union all
SELECT'李四','2008-4-10',200
Go
--Start
SELECT
A.*,B.nameriqi,B.JINE,
C.nameriqi,C.JINE,
D.nameriqi,D.JINE,
E.nameriqi,E.JINE,
F.nameriqi,F.JINE,
G.nameriqi,G.JINE,
H.nameriqi,H.JINE,
I.nameriqi,I.JINE,
J.nameriqi,J.JINE,
K.nameriqi,K.JINE
FROM
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=1
) A LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=2
)B ON A.NAME=B.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=3
)C ON A.NAME=C.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=4
)D ON A.NAME=D.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=5
)E ON A.NAME=E.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=6
)F ON A.NAME=F.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=7
)G ON A.NAME=G.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=8
)H ON A.NAME=H.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=9
)I ON A.NAME=I.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=10
)J ON A.NAME=J.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=11
)K ON A.NAME=K.NAME LEFT JOIN
(
Select NAME,nameriqi,JINE from TB WHERE DATEPART(MM,nameriqi)=12
)L ON A.NAME=L.NAME
--Result:
/*
（所影响的行数为 10 行）
NAME nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE nameriqi JINE
---------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ -----------
李四 2008-01-10 00:00:00 200 2008-02-10 00:00:00 200 2008-03-10 00:00:00 200 2008-04-10 00:00:00 200 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
张三 2008-01-01 00:00:00 100 2008-02-01 00:00:00 100 2008-03-01 00:00:00 100 2008-04-01 00:00:00 100 2008-05-01 00:00:00 100 2008-06-01 00:00:00 100 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
（所影响的行数为 2 行）
*/
--End 很死的写法
liangCK 2009-11-16
- 打赏
- 举报
declare @i int;
set @i = (select top 1 cnt
from (select count(*) as cnt from tb group by name) as t
order by cnt desc);
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',max(case when rowid ='+rtrim(number)+' then riqi end)'
+ ',max(case when rowid ='+rtrim(number)+' then jine end)'
from master.dbo.spt_values
where type = 'p' and number < @i;
exec('select name'+@sql+'
from (select *,rowid=row_number() over(partition by name order by riqi)
from tb) as t
group by name');liherun 2009-11-16
- 打赏
- 举报
不好意思，枚说明白，同一姓名 最多有10条，有可能1条有可能10条 最多有10条
好汉坡 2009-11-16
- 打赏
- 举报
要是有N个月，M天就完了
