将行中的值用于列 [英] Use values in Rows into columns
问题描述
我有两张桌子的价值
ID姓名地址
1 Moses NGL
ID通行证到期用户ID
1 ABC 26/11/14 1
2 EFG 29/12/14 1 < br $>
我需要查询结构
ID名称地址Pass1到期1通行证2到期2
1摩西NGL ABC 26/11/14 EFG 29/12/14
有没有办法做到这一点
只是一个简单的加入 [ ^ ] !!!
尝试这些,可能会有所帮助。
- < span class =code-comment> 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 tables
ID Name Address
1 Moses NGL
ID Pass Expiry UserID
1 ABC 26/11/14 1
2 EFG 29/12/14 1
I Need an query with the structure
ID Name Address Pass1 Expiry1 Pass2 Expiry2
1 Moses NGL ABC 26/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 = 1
alter FUNCTION dbo.fn_GetHandlingCodes ()
RETURNS VARCHAR(1200)
AS
BEGIN
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 @ReturnValue
END
这篇关于将行中的值用于列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!