写一sql 拆分列

lifeiscool 2009-10-28 09:46:50

table1

渠道编号渠道名称时间
id1 name1 2009-10-05
id2 name2 2009-10-05
id3 name3 2009-10-05
id4 name4 2009-10-06
id5 name5 2009-10-06
id6 name6 2009-10-06
id7 name7 2009-10-06

要转换成这个格式
渠道编号渠道名称 5 6
id1 name1 2009-10-05
id2 name2 2009-10-05
id3 name3 2009-10-05
id4 name4 2009-10-06
id5 name5 2009-10-06、

不知道是否描述清楚了?大哥大姐们帮帮忙..

...全文

88 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

lifeiscool 2009-10-28

打赏
举报

谢谢各位了.. 懂了结贴..

--小F-- 2009-10-28

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-10-28 09:57:53

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([渠道编号] varchar(3),[渠道名称] varchar(5),[时间] datetime)

insert [tb]

select 'id1','name1','2009-10-05' union all

select 'id2','name2','2009-10-05' union all

select 'id3','name3','2009-10-05' union all

select 'id4','name4','2009-10-06' union all

select 'id5','name5','2009-10-06' union all

select 'id6','name6','2009-10-06' union all

select 'id7','name7','2009-10-06'

--------------开始查询--------------------------

select

  渠道编号, 渠道名称 , 

  case when datename(day,时间)=5 then convert(varchar(10),时间,120) else '' end as [5] , 

  case when datename(day,时间)=6 then convert(varchar(10),时间,120) else '' end  as [6]  

from 

  tb 

----------------结果----------------------------

/* 渠道编号 渠道名称  5          6

---- ----- ---------- ----------

id1  name1 2009-10-05 

id2  name2 2009-10-05 

id3  name3 2009-10-05 

id4  name4            2009-10-06

id5  name5            2009-10-06

id6  name6            2009-10-06

id7  name7            2009-10-06



(7 行受影响)



*/

xiequan2 2009-10-28

打赏
举报

--> 测试数据：[table1]

if object_id('[table1]') is not null drop table [table1]

go

create table [table1]([渠道编号] varchar(3),[渠道名称] varchar(5),[时间] datetime)

insert [table1]

select 'id1','name1','2009-10-05' union all

select 'id2','name2','2009-10-05' union all

select 'id3','name3','2009-10-05' union all

select 'id4','name4','2009-10-06' union all

select 'id5','name5','2009-10-06' union all

select 'id6','name6','2009-10-06' union all

select 'id7','name7','2009-10-06'



 select 渠道编号, 渠道名称 , 

[5]= case when DATEPART(day,时间)=5 then convert(varchar(10),时间,120) else '' end  , 

[6]= case when DATEPART(day,时间)=6 then convert(varchar(10),时间,120) else '' end   

from 

table1  



/*

渠道编号 渠道名称  5          6

---- ----- ---------- ----------

id1  name1 2009-10-05 

id2  name2 2009-10-05 

id3  name3 2009-10-05 

id4  name4            2009-10-06

id5  name5            2009-10-06

id6  name6            2009-10-06

id7  name7            2009-10-06

*/

水族杰纶 2009-10-28

打赏
举报

6,7哪裡去了?

华夏小卒 2009-10-28

打赏
举报

没看懂

SQL77 2009-10-28

打赏
举报

[Quote=引用 1 楼 feixianxxx 的回复:]
SQL codeselect
渠道编号, 渠道名称 ,[5]=MAX(casewhenDATEPART(day,时间)=5thenconvert(varchar(10),时间,120)else''end ),[6]=MAX(casewhenDATEPART(day,时间)=6thenconvert(varchar(10),时间,120)else''end )from table1groupby¡
[/Quote]
晕

feixianxxx 2009-10-28

打赏
举报

 select 

渠道编号,  渠道名称 ,

[5]=MAX(case when DATEPART(day,时间)=5 then convert(varchar(10),时间,120) else '' end ),

[6]=MAX(case when DATEPART(day,时间)=6 then convert(varchar(10),时间,120) else '' end )

from table1

group by 渠道编号,  渠道名称