34,576
社区成员
发帖
与我相关
我的任务
分享
----------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 行受影响)
-------------------------------------
-- 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
*/
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');
李四 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-01
李四 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
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');
/*
-- 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
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');