数据库数据横排显示

最新推荐文章于 2021-10-23 15:52:27 发布

__BMGT()

最新推荐文章于 2021-10-23 15:52:27 发布

阅读量656

点赞数

分类专栏： Database

本文链接：https://blog.csdn.net/xiuye2015/article/details/50439620

版权

Database 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

L_ADVANTAGE11.1?if object_id('#case') is not null
	drop table #case

create table #case( id int identity ,
issue_type varchar(20) null,
asset_id int null)
go    
 insert into #case select 'for  issue',	1701335119                           
insert into #case(issue_type,asset_id) select 'for  issue',	1701335120       
insert into #case(issue_type,asset_id) select 'for  issue',	1701335121       
insert into #case(issue_type,asset_id) select 'for  issue',	1701335127       
insert into #case(issue_type,asset_id) select 'for  issue',	1701335130       
insert into #case(issue_type,asset_id) select 'for21 issue',	1701335131    
insert into #case(issue_type,asset_id) values ('for21 issue',	1701335132)    
insert into #case(issue_type,asset_id) values ('for21 issue',	1701335135)    
insert into #case(issue_type,asset_id) values ('for21 issue',	1701335140)    
go
select * from #case



/*
方案1
*/

DECLARE c cursor for select id from #case
go 
DECLARE 
		@a varchar(300),
		@b varchar(300),
		@id int,
		@i int,
		@count int		
		open c
		select @count = count(*) from #case
		SET @a = null
		SET @b = null
		fetch c into @id
		SET @i = 1
WHILE @i < @count+1
BEGIN
	IF  (SELECT issue_type FROM #case WHERE id = @id) = 'for  issue'
		SET @a = @a
					+'|'
					+CONVERT(varchar(20),(SELECT asset_id FROM #case WHERE id = @id))
    ELSE
		 SET @b = @b
					 +'|'
					 +CONVERT(varchar(20),(SELECT asset_id FROM #case WHERE id = @id))
	set @i = @i + 1
	fetch c into @id	
END
close c
deallocate c
 SET @a = SUBSTRING(@a,2,CHAR_LENGTH(@a))
 SET @b = SUBSTRING(@b,2,CHAR_LENGTH(@b))	
SELECT issue_type,CASE issue_type
                  WHEN 'for  issue'
                  THEN @a
                  ELSE @b END
		asset_id
FROM #case
GROUP BY issue_type

issue_type           asset_id                                                                                                                                                                                                                                                                                                     
----------           --------                                                                                                                                                                                                                                                                                                     
for21 issue          1701335131|1701335132|1701335135|1701335140                                                                                                                                                                                                                                                                  
for  issue           1701335119|1701335120|1701335121|1701335127|1701335130