SQL表结构转换（一个SQL面试题）

最新推荐文章于 2024-07-28 03:27:59 发布

kevin

最新推荐文章于 2024-07-28 03:27:59 发布

阅读量999

点赞数

分类专栏： MS SQL Server2K/2K5 文章标签： sql 面试 insert table

本文链接：https://blog.csdn.net/DreamTww/article/details/4364711

版权

MS SQL Server2K/2K5 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

将TestA转换成TestB的结构 /************************************************************************************* TestA TestB Code Value Code Value 1 1 1 1,2 1 2 2 5,6 2 5 3 0,9 2 6 3 0 3 9 *************************************************************************************/ Create table TestA ( code int, value varchar(100) ) Insert into TestA select 1, '1' Insert into TestA select 1, '2' Insert into TestA select 2, '5' Insert into TestA select 2, '6' Insert into TestA select 3, '0' Insert into TestA select 3, '9' declare @Code int declare @VarValue varchar(100) set @VarValue = '' select top 0 * into #TestB from TestA declare Cur_Code Cursor for select distinct code From TestA Open Cur_Code Fetch Next From Cur_Code Into @Code while @@Fetch_Status = 0 begin select @VarValue = @VarValue +','+value From TestA where Code = @Code set @VarValue = Right(@VarValue, len(@VarValue)- 1 ) Insert into #TestB Select @Code, @VarValue set @varValue = '' Fetch Next From Cur_Code Into @Code end Close Cur_Code Deallocate Cur_Code Select * From TestA select * From #TestB Drop table #TestB

总结：

此处主要用到一个SQL拼接的技巧，如下：

select @VarValue = @VarValue +','+value From TestA where Code = @Code

解析：如 @code = 1

@varName = ',1,2'

这句SQL可以把横向转到纵向。