客户充值周期表：
UserID date
1941 2014-05-31
1941 2014-06-04
2064 2013-03-20
2064 2013-11-25
2064 2014-10-24
3066 2013-01-06
3066 2013-07-05
3066 2014-01-09
3420 2013-11-23
3420 2013-11-09
3420 2013-11-23
3420 2014-10-01
3420 2014-08-06
3420 2014-10-01
3907 2013-05-17
3907 2014-04-17
3907 2013-03-19
3907 2014-11-20
如何转置成下表：
UserID 日期1 日期2 日期3 日期4 日期5 日期6
1941 2014-05-31 2014-06-04
2064 2013-03-20 2013-11-25 2014-10-24
3066 2013-01-06 2013-07-05 2014-01-09
3420 2013-11-23 2013-11-09 2013-11-23 2014-10-01 2014-08-06 2014-10-01
3907 2013-05-17 2014-04-17 2013-03-19 2014-11-20
------解决思路----------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-12-09 08:46:28
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([UserID] int,[date] datetime)
insert [tb]
select 1941,'2014-05-31' union all
select 1941,'2014-06-04' union all
select 2064,'2013-03-20' union all
select 2064,'2013-11-25' union all
select 2064,'2014-10-24' union all
select 3066,'2013-01-06' union all
select 3066,'2013-07-05' union all
select 3066,'2014-01-09' union all
select 3420,'2013-11-23' union all
select 3420,'2013-11-09' union all
select 3420,'2013-11-23' union all
select 3420,'2014-10-01' union all
select 3420,'2014-08-06' union all
select 3420,'2014-10-01' union all
select 3907,'2013-05-17' union all
select 3907,'2014-04-17' union all
select 3907,'2013-03-19' union all
select 3907,'2014-11-20'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select UserID '
select @sql = @sql + ' , max(case px when ''' + ltrim(px) + ''' then date else '''' end) [日期' + ltrim(px) + ']'
from (select distinct px from (select px=row_number()over(partition by UserID order by getdate()),* from tb) as t) as a
set @sql = @sql + ' from (select px=row_number()over(partition by UserID order by getdate()),* from tb) as t group by UserID'
exec(@sql)
----------------结果----------------------------
/* UserID 日期1 日期2 日期3 日期4 日期5 日期6
----------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
1941 2014-05-31 00:00:00.000 2014-06-04 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
2064 2013-03-20 00:00:00.000 2013-11-25 00:00:00.000 2014-10-24 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
3066 2013-01-06 00:00:00.000 2013-07-05 00:00:00.000 2014-01-09 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
3420 2013-11-23 00:00:00.000 2013-11-09 00:00:00.000 2013-11-23 00:00:00.000 2014-10-01 00:00:00.000 2014-08-06 00:00:00.000 2014-10-01 00:00:00.000
3907 2013-05-17 00:00:00.000 2014-04-17 00:00:00.000 2013-03-19 00:00:00.000 2014-11-20 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
(5 行受影响)
*/
------解决思路----------------------
declare @sql varchar(8000)
set @sql = 'select UserID '
;with cteStart(UserID,date) as(
select '1941','2014-05-31' union all
select '1941','2014-06-04' union all
select '2064','2013-03-20' union all
select '2064','2013-11-25' union all
select '2064','2014-10-24' union all
select '3066','2013-01-06' union all
select '3066','2013-07-05' union all
select '3066','2014-01-09' union all
select '3420','2013-11-23' union all
select '3420','2013-11-09' union all
select '3420','2013-11-23' union all
select '3420','2014-10-01' union all
select '3420','2014-08-06' union all
select '3420','2014-10-01' union all
select '3907','2013-05-17' union all
select '3907','2014-04-17' union all
select '3907','2013-03-19' union all
select '3907','2014-11-20'
),
cte as(
select UserID,date,
row_number() over(partition by UserID order by UserID,date) as sn
from cteStart
)
select *
into #cte
from cte
select @sql = @sql + ' , max(case sn when ''' + sn + ''' then date else '''' end) [日期' +sn + ']'
from (select distinct sn from #cte) as t
set @sql=@sql+' from #cte group by UserID';
exec(@sql)
--print @sql
drop table #cte
UserID 日期1 日期2 日期3 日期4 日期5 日期6
------ ---------- ---------- ---------- ---------- ---------- ----------
1941 2014-05-31 2014-06-04
2064 2013-03-20 2013-11-25 2014-10-24
3066 2013-01-06 2013-07-05 2014-01-09
3420 2013-11-09 2013-11-23 2013-11-23 2014-08-06 2014-10-01 2014-10-01
3907 2013-03-19 2013-05-17 2014-04-17 2014-11-20
(5 行受影响)