sql server2005列转行[分不够，解决后再加分]

老杨_sz 2011-03-16 05:59:11



表A数据：

－－－－－－－－－－－－

192.168.1.211/01	2011-03-01 	09:11:06

192.168.1.211/01	2011-03-01 	11:18:59

192.168.1.211/01	2011-03-02 	10:14:05

192.168.1.211/01	2011-03-02 	11:19:05

192.168.1.211/01	2011-03-02 	15:10:37

192.168.1.211/01	2011-03-03 	10:10:33

192.168.1.211/01	2011-03-04 	10:16:35

192.168.1.211/01	2011-03-04 	11:12:02

192.168.1.212/02	2011-03-16 	16:31:18

192.168.1.212/02	2011-03-16 	17:01:17

192.168.1.213/03	2011-03-03 	18:04:59

192.168.1.213/03	2011-03-03 	18:36:38



求如下结果：

－－－－－－－－－－－－

IP地址			2011-03-01		2011-03-02			2011-03-03	2011-03-04	2011-03-05	...	2011-03-16	...

192.168.1.211/01	09:11:06;11:18:59;	10:14:05;11:19:05;15:10:37	10:10:33	10:16:35;11:12:02

192.168.1.212/02															16:31:18;17:01:17

192.168.1.213/03        							18:04:59;18:36:38

...全文

66 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

中国风 2011-03-17

打赏
举报

靜態方法



use Tempdb

go

--> --> 

 

if not object_id(N'Tempdb..#1') is null

	drop table #1

Go

Create table #1([Col1] nvarchar(16),[Col2] NVARCHAR(10),[Col3] NVARCHAR(10))

Insert #1

select N'192.168.1.211/01','2011-03-01','09:11:06' union all

select N'192.168.1.211/01','2011-03-01','11:18:59' union all

select N'192.168.1.211/01','2011-03-02','10:14:05' union all

select N'192.168.1.211/01','2011-03-02','11:19:05' union all

select N'192.168.1.211/01','2011-03-02','15:10:37' union all

select N'192.168.1.211/01','2011-03-03','10:10:33' union all

select N'192.168.1.211/01','2011-03-04','10:16:35' union all

select N'192.168.1.211/01','2011-03-04','11:12:02' union all

select N'192.168.1.212/02','2011-03-16','16:31:18' union all

select N'192.168.1.212/02','2011-03-16','17:01:17' union all

select N'192.168.1.213/03','2011-03-03','18:04:59' union all

select N'192.168.1.213/03','2011-03-03','18:36:38'

Go

;WITH C

AS

(

Select 

	[Col1],[Col2],[Col3]=STUFF((SELECT ';'+[Col3] FROM #1 WHERE [Col1]=a.[Col1] AND [Col2]=a.[Col2] FOR XML PATH('')),1,1,'')

from #1 AS a 

GROUP BY [Col1],[Col2]

)

SELECT 

[2011-03-01]=max(CASE WHEN [Col2]='2011-03-01' THEN [Col3] ELSE '' END),

[2011-03-02]=max(CASE WHEN [Col2]='2011-03-02' THEN [Col3] ELSE '' END),

[2011-03-03]=max(CASE WHEN [Col2]='2011-03-03' THEN [Col3] ELSE '' END),

............

FROM C

GROUP BY [Col1]