本文介绍了将行中的值用于列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 我有两张桌子的价值 ID姓名地址 1 Moses NGL ID通行证到期用户ID 1 ABC 26/11/14 1 2 EFG 29/12/14 1 我需要查询结构 ID名称地址Pass1到期1通行证2到期2 1摩西NGL ABC 26/11/14 EFG 29/12/14 有没有办法做到这一点解决方案 只是一个简单的加入 [ ^ ] !!! 尝试这些,可能会有所帮助。 - create table sampled1(id int,name varchar(40),address varchar(100)) - create table sampled2(id int,pass varchar(40),expiry datetime,userid int) - 插入samples1值(1,'moses','ngl') - 插入samples2值(1,'abc','1-1-2014',1 ) - 插入samples2值(2,'EF','1-1 -2014',1) SELECT id,dbo.fn_GetHandlingCodes() FROM sampled2 其中 id = 1 改变功能dbo.fn_GetHandlingCodes() RETURNS VARCHAR(1200) AS BEGIN DECLARE @HandlingCode VARCHAR(20) DECLARE @Handling Code1 VARCHAR(20) DECLARE @ReturnValue VARCHAR(4000) - 使用最快的游标方法:本地fast_forward DECLARE code_cursor CURSOR LOCAL fast_forward FOR SELECT pass,expiry FROM sampled2 SET @ReturnValue ='' - 设置为非null OPEN code_cursor FETCH NEXT FROM code_cursor INTO @ HandlingCode,@ HandlingCode1 WHILE(@@ FETCH_STATUS = 0) BEGIN SET @ReturnValue = @ReturnValue + @HandlingCode +','+ @ HandlingCode1 +',' 如果LEN(@ReturnValue)> 1000 BREAK - 避免溢出 FETCH NEXT FROM code_cursor INTO @ HandlingCode,@ HandlingCode1 END 关闭code_cursor DEALLOCATE code_cursor - 删除最后一个分隔符 如果LEN (@ReturnValue)> 1 SET @ReturnValue = SUBSTRING(@ ReturnValue,1,LEN(@ReturnValue)-2) RETURN @ReturnValue END I have values in two tablesID Name Address1 MosesNGLID PassExpiryUserID1 ABC26/11/14 12 EFG 29/12/14 1I Need an query with the structureID Name Address Pass1Expiry1 Pass2Expiry21 MosesNGL ABC26/11/14 EFG 29/12/14 Is there any way to do that 解决方案 Just a simple JOIN[^] !!!Try these, May be it will helpfull.--create table sampled1(id int,name varchar(40),address varchar(100))--create table sampled2(id int, pass varchar(40),expiry datetime,userid int)--insert into sampled1 values(1,'moses','ngl')--insert into sampled2 values(1,'abc','1-1-2014',1)--insert into sampled2 values(2,'EF','1-1-2014',1)SELECT id,dbo.fn_GetHandlingCodes ()FROM sampled2 where id = 1alter FUNCTION dbo.fn_GetHandlingCodes ()RETURNS VARCHAR(1200)ASBEGIN DECLARE @HandlingCode VARCHAR(20) DECLARE @HandlingCode1 VARCHAR(20) DECLARE @ReturnValue VARCHAR(4000)-- use that fastest cursor methods: local fast_forward DECLARE code_cursor CURSOR LOCAL fast_forward FOR SELECT pass,expiry FROM sampled2 SET @ReturnValue = '' -- set to non null OPEN code_cursor FETCH NEXT FROM code_cursor INTO @HandlingCode,@HandlingCode1 WHILE (@@FETCH_STATUS = 0) BEGIN SET @ReturnValue = @ReturnValue + @HandlingCode + ', ' + @HandlingCode1 +', ' IF LEN (@ReturnValue) > 1000 BREAK -- avoid overflow FETCH NEXT FROM code_cursor INTO @HandlingCode,@HandlingCode1 END CLOSE code_cursor DEALLOCATE code_cursor-- remove last delimiter IF LEN(@ReturnValue) > 1 SET @ReturnValue = SUBSTRING(@ReturnValue,1,LEN(@ReturnValue)-2) RETURN @ReturnValueEND 这篇关于将行中的值用于列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
10-18 21:22