SQL Query将两行连接成单个字符串。 [英] SQL Query to concatenate two row into single string.

问题描述

表1

Table 1

Id	SegId	Fid	Amount
1	  7,8	101	200
2	    9	101	300

表2

Table 2

SegId	Fid	Orgin	Destination
7       101	  DEL	  BOM
8       101	  BOM	  GOA
9	  101	  DEL	  BOM

我有两个表1和表2我想要sql查询找到预期的结果

表-3

I have two table table 1 and table 2 i want sql query to find expected result in
table -3

Fid	Segments	Amount
101	DEL-BOM-GOA	 200
101	DEL-BOM	         300

提前致谢

Thanks in advance

推荐答案

以下查询将为您提供所需的结果。

Following query will give the required result for you.

SELECT
        Fid,
        (SELECT
            top 1
                STUFF(
                    (SELECT
                            '-' +  T4.Orgin
                        FROM
                            dbo.fnSplitString( ot.SegId ,',')  t3
                            join table2 t4 on t3.splitdata = t4.segid FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
                + '-' +  t2.Destination
                FROM
            dbo.fnSplitString( ot.SegId ,',')  t1 join table2 t2 on t1.splitdata = t2.segid order by t1.splitdata desc)
         AS Segments,
        Amount,*
    FROM TABLE1 ot

在上面的查询中fnSplitString（）用户定义的函数已被用于此函数的代码是

In the above query fnSplitString() user defined function has been used the code for this function is

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

取自如何通过分隔符拆分字符串SQL Server中的char .............. - SQLServerCentral [ ^ ]



祝你好运！但是如果可能的话，尝试更改表结构：）

Taken from How to Split a string by delimited char in SQL Server.............. - SQLServerCentral[^]

Good luck! But try to change the table structure if possible :)

这篇关于SQL Query将两行连接成单个字符串。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！