SQL Server2005 如何合并行?

zhousqy 2011-01-05 01:06:31

荒废SQL很久了,求如下功能的代码或思路,谢了.

合并前: xxx_1, a, , ,
xxx_2, ,b, ,
xxx_3, , ,c,
yyy_1, e, , ,
yyy_2, ,f, ,
yyy_3, , ,d,
合并后: xxx,a,b,c
yyy,e,f,d

是不是只能用case来分情况取字段的值,比如_1的时候,取a字段...

...全文

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

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhousqy 2011-01-05

打赏
举报

原来可以用GROUP BY LEFT(ID, LEN(ID) - CHARINDEX('_', REVERSE(ID)))

zhousqy 2011-01-05

打赏
举报

请问类似CHARINDEX,有反向查找的函数吗?因为有ID可能是xxx_xx_xx_1

飘零一叶 2011-01-05

打赏
举报

create table tb
(
id varchar(10),
a varchar(10),
b varchar(10),
c varchar(10),
)
insert into tb
select 'xxx_1','a','','' union all
select 'xxx_2','','b','' union all
select 'xxx_3','','','c' union all
select 'yyy_1','e','','' union all
select 'yyy_2','','f','' union all
select 'yyy_3','','','d'

select LEFT(id,3),MAX(a) a,MAX(b) b,MAX(c) c
from tb group by LEFT(id,3)
------------------------
(无列名) a b c
xxx a b c
yyy e f d

zhousqy 2011-01-05

打赏
举报

[Quote=引用 3 楼 guguda2008 的回复:]

SQL code
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID VARCHAR(10)
,VAL1 VARCHAR(10)
,VAL2 VARCHAR(10)
,VAL3 VARCHAR(10)
)
INSERT INTO TB
SELECT 'xxx_1','a',' ',' ' UNION ALL
……
[/Quote]
看下哈,谢谢

guguda2008 2011-01-05

打赏
举报

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

GO

CREATE TABLE TB(

ID VARCHAR(10)

,VAL1 VARCHAR(10)

,VAL2 VARCHAR(10)

,VAL3 VARCHAR(10)

)

INSERT INTO TB

SELECT 'xxx_1','a',' ',' ' UNION ALL 

SELECT 'xxx_2',' ','b',' ' UNION ALL 

SELECT 'xxx_3',' ',' ','c' UNION ALL 

SELECT 'yyy_1','e',' ',' ' UNION ALL  

SELECT 'yyy_2',' ','f',' ' UNION ALL 

SELECT 'yyy_3',' ',' ','d'



SELECT LEFT(ID,CHARINDEX('_',ID)-1),MAX(VAL1),MAX(VAL2),MAX(VAL3)

FROM TB

GROUP BY LEFT(ID,CHARINDEX('_',ID)-1)

/*

xxx        a          b          c

yyy        e          f          d

*/