拆分在SQL中的一列，然后查找每一个在另一个表 [英] Splitting a column in SQL, then looking up each one in another table

问题描述

我不知道这是否可能在SQL中，如果没有，我会编程做到这一点 - 但它会是巨大的，如果有办法

I'm not sure if this is possible in SQL, and if not I'll do it programatically - but it'd be great if there was a way.

基本上，我在其中获取与ID的另一个表，用逗号分隔的填充一个表中的列 - 这样一个领域可能是这样的：

Basically, I have a column in a table which gets populated with ID's from another table, seperated by commas - so a field might look like this:

3,4,9

这些链接到另一个表，其中上述字段中的数字是一个行的主键。在该行的描述，这是我要显示给用户，而不是数字。

These link to another table, where the numbers in the field above are the primary key of a row. In the row is a description, which I want to display to the user rather than numbers.

所以基本上，而不是3,4,9显示给用户，我想看看他们的相关描述另一个表。这可能吗？

So basically, rather than display 3,4,9 to the user, I want to look up their relevant descriptions in another table. Is this possible?

推荐答案

克里斯，

有一个办法在T-SQL来做到这一点，它实际上是pretty容易。首先，你需要创建像下面......一个函数（全新采用零基理货CTE方法，它是讨厌的快，因为它没有任何串联分隔符）。

There IS a way to do this in T-SQL and it's actually pretty easy. First, you need to create a function like the following... (brand new method using zero based "Tally" cte, it's nasty fast because it doesn't concatenate any delimiters).

 CREATE FUNCTION dbo.DelimitedSplit8KNEW
--===== Created by Jeff Moden (Prototype: Testing Still in Progress)
--===== Define I/O parameters
        (
        @pString    VARCHAR(8000),
        @pDelimiter CHAR(1)
        )
RETURNS TABLE
   WITH SCHEMABINDING
     AS
 RETURN
WITH 
      E1(N) AS (
                SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
               ),                          --10
      E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
      E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (
                SELECT 0 UNION ALL
                SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4
               )
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY t.N),
        ItemValue  = SUBSTRING(@pString,t.N+1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,t.N+1),0),DATALENGTH(@pString)+1)-t.N-1)
   FROM cteTally t
  WHERE t.N BETWEEN 0 AND DATALENGTH(@pString)
    AND (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
;
GO

现在，看看如何使用它？让我们说你有一个独特的列和您的CSV列的表如下（此构建的数据，以及）。我们所要做的就是CROSS的功能应用原始数据和数据分割神奇地准备与另一个表加入的：

Now, to see how it's used... let's say you have a table with a unique column and your CSV column as follows (this builds the data, as well). All we have to do is CROSS APPLY the original data with the function and the data is magically split ready to be joined with another table:

--===== This just builds some test data 
     -- and is not a part of the solution
 SELECT *
   INTO #TestTable
   FROM (
         SELECT 1,'3,4,9'   UNION ALL
         SELECT 2,'3,2,100' UNION ALL
         SELECT 3,'14,35,8,21,27,12'
        ) d (RowNum,CsvValue)
;
--===== Split the data out giving the unique RowNum
     -- from the original data, the element position,
     -- the the value of the split element.  You can
     -- join this SELECT with a table to get the other
     -- values.
 SELECT data.RowNum, split.ItemNumber, split.ItemValue
   FROM #TestTable data
  CROSS APPLY dbo.DelimitedSplit8KNEW(data.CsvValue,',') split
;

下面是输出...

RowNum      ItemNumber           ItemValue
----------- -------------------- ---------
1           1                    3
1           2                    4
1           3                    9
2           1                    3
2           2                    2
2           3                    100
3           1                    14
3           2                    35
3           3                    8
3           4                    21
3           5                    27
3           6                    12

(12 row(s) affected)

这篇关于拆分在SQL中的一列，然后查找每一个在另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！