请问重复数据行列互换有关问题
请教重复数据行列互换问题
RT
-------------
TABLE
ID DATETIME CODE
1 2012-7-7 A
1 2012-7-7 B
1 2012-7-17 C
1 2012-7-24 D
2 2012-7-11 A
2 2012-7-11 B
3 2012-7-16 A
3 2012-7-16 B
3 2012-7-17 C
3 2012-7-23 D
----------------------
如何实现如下查询结果?3Q
ID A B C D
1 2012-7-7 2012-7-7 2012-7-17 2012-7-24
2 2012-7-11 2012-7-11 NULL NULL
3 2012-7-16 2012-7-16 2012-7-17 2012-7-23
------解决方案--------------------
RT
-------------
TABLE
ID DATETIME CODE
1 2012-7-7 A
1 2012-7-7 B
1 2012-7-17 C
1 2012-7-24 D
2 2012-7-11 A
2 2012-7-11 B
3 2012-7-16 A
3 2012-7-16 B
3 2012-7-17 C
3 2012-7-23 D
----------------------
如何实现如下查询结果?3Q
ID A B C D
1 2012-7-7 2012-7-7 2012-7-17 2012-7-24
2 2012-7-11 2012-7-11 NULL NULL
3 2012-7-16 2012-7-16 2012-7-17 2012-7-23
------解决方案--------------------
- SQL code
create table tb(ID int, DATETIME datetime ,code varchar(2)) insert into tb values('1','2012-7-7','A') insert into tb values('1','2012-7-7','B') insert into tb values('1','2012-7-17','C') insert into tb values('1','2012-7-24','D' ) go select ID, CODE,[DATETIME]=stuff((select ','+[DATETIME] from tb where ID=tb.ID for xml path('')), 1, 1, '') from tb group by ID /* ID A B C D ----------- -------------------- 1 2012-7-7 2012-7-7 2012-7-17 2012-7-24
------解决方案--------------------
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
------解决方案--------------------
- SQL code
if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[DATETIME] datetime,[CODE] varchar(1)) insert [tb] select 1,'2012-7-7','A' union all select 1,'2012-7-7','B' union all select 1,'2012-7-17','C' union all select 1,'2012-7-24','D' union all select 2,'2012-7-11','A' union all select 2,'2012-7-11','B' union all select 3,'2012-7-16','A' union all select 3,'2012-7-16','B' union all select 3,'2012-7-17','C' union all select 3,'2012-7-23','D' go declare @sql varchar(8000) select @sql=isnull(@sql+',','') +'max(case when code='''+code+''' then convert(varchar(10),datetime,120) end) as ['+code+']' from (select distinct code from tb) t exec('select id,'+@sql+' from tb group by id') /** id A B C D ----------- ---------- ---------- ---------- ---------- 1 2012-07-07 2012-07-07 2012-07-17 2012-07-24 2 2012-07-11 2012-07-11 NULL NULL 3 2012-07-16 2012-07-16 2012-07-17 2012-07-23 (3 行受影响) **/