主页 / dba / 问题 / 28172
Accepted
JoeGeeky
Asked: 2012-11-06 02:18:37 +0800 CST

旋转行内容以产生一维结果

  • 772

我有一个包含竖线分隔值的 varchar 列的表

例如：

Row 1 Column 1 = a|b|e|gg|foo 
Row 2 Column 1 = oV|foo|do 
Row 3 Column 1 = boop

我如何查询它以返回类似的东西？重复是可以的。

结果：

column
a
b
e
gg
foo
foo
oV
do
boop

我意识到这不是最优的，但我的手被当前的模式束缚了

sql-server t-sql

3 个回答

  1. Best Answer

    如果您无法更改当前模式，则可以创建一个拆分函数：

    create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

    然后你可以使用 anouter apply来加入你的表：

    select x.items
from yourtable t
outer apply dbo.split(col1, '|') x;

    这将产生以下结果：

    | ITEMS |
---------
|     a |
|     b |
|     e |
|    gg |
|   foo |
|    oV |
|   foo |
|    do |
|  boop |

    请参阅带有演示的 SQL Fiddle

    或者，如果您不想使用拆分函数，则可以使用 CTE 执行此操作：

    ;with cte (value, fullValue) as
(
  select 
      cast(left(col1, charindex('|',col1+'|')-1) as varchar(50)) value,
      stuff(col1, 1, charindex('|',col1+'|'), '') fullValue
  from yourtable
  union all
  select 
    cast(left(fullValue, charindex('|',fullValue+'|')-1) as varchar(50)) ,
    stuff(fullValue, 1, charindex('|',fullValue+',|'), '') fullValue
  from cte
  where fullValue > ''
) 
select value
from cte

    请参阅带有演示的 SQL Fiddle

    • 1

  2. 假设您的数据包含普通的字母数字字符并且没​​有任何东西会破坏 XML，您可以使用此查询执行一些文本操作以使您的数据看起来像 XML，然后执行 XQuery 来分割列。

    SELECT N.item.value('.','nvarchar(max)') item
FROM
(
     SELECT CAST('<a>'+REPLACE(Data,'|','</a><a>')+'</a>' as xml) X
       FROM Tbl T
) G
CROSS APPLY X.nodes('a') N(item)

    MS SQL Server 2012 架构设置：

    CREATE TABLE Tbl (Data varchar(max));
INSERT Tbl VALUES
  ('a|b|e|gg|foo'),
  ('oV|foo|do'), 
  ('boop');

    结果（SQL 小提琴）：

    | ITEM |
--------
|    a |
|    b |
|    e |
|   gg |
|  foo |
|   oV |
|  foo |
|   do |
| boop |
    • 0

  3. 比使用 XML 或函数更快的方法是使用计数表。以下代码改编自我最强烈推荐的一篇优秀的 Jeff Moden 文章。

    create table #test
(
    Id int,
    string varchar(30)
);
insert into #test(Id, string) values
(1, 'a|b|e|gg|foo'),
(2, 'oV|foo|do'),
(3, 'boop');

declare @maxlen int;
select @maxlen = MAX(LEN(string)) from #test;
declare @delimiter char(1) = '|';

--A tally table generated from a CTE.  For small sizes (10K, at least)
--the CTE solution is markedly faster than a standard tally table.
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
),                                      --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b),    --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b),    --10E+4 or 10,000 rows max
cteTally(N) AS
(
    SELECT 0 UNION ALL
    SELECT TOP (@maxlen) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(Id, N1) AS
(
    SELECT
        test.Id,
        t.N + 1
    FROM
        cteTally t
        cross join #Test test
    WHERE
        SUBSTRING(test.String, t.N, 1) = @delimiter
        OR t.N = 0
)
--      Do the actual split. The ISNULL/NULLIF combo handles the length
--      for the final element when no delimiter is found.
 SELECT test.Id,
        ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = SUBSTRING(test.String, s.N1, ISNULL(NULLIF(CHARINDEX(@delimiter,test.String,s.N1),0)-s.N1,8000))
   FROM cteStart s
        cross join #Test test
  WHERE s.Id = test.Id;

drop table #test;

    代码有点复杂，但性能明显更好，尤其是当您需要解析的行数增加时。

    前几个 CTE（E1、E2 和 E4）只是在设置计数表。然后 cteTally CTE 动态生成最佳计数表长度（表中最长字符串的长度）。然后，cteStart 找到所有的分离点。您的数据集的结果如下所示：

    +----+----+
| Id | N1 |
+----+----+
|  1 |  1 |
|  2 |  1 |
|  3 |  1 |
|  1 |  3 |
|  2 |  4 |
|  1 |  5 |
|  1 |  7 |
|  2 |  8 |
|  1 | 10 |
+----+----+

    解释这个有点棘手，因为该过程在第一个位置添加了一个伪分隔符（如果尚不存在），这是一个单索引字符串。因此，第 1、2 和 3 行的 N1 = 1。3 不再有分隔符。1 在位置 3、5、7 和 10 处有额外的分隔符，再次注意到字符“a”实际上位于此设置中的位置 2。

    无论如何，最终查询会进行实际拆分并返回所需的数据集。

    计数表拆分是字符串拆分问题的最佳非 CLR 解决方案。如果您的行少于大约 10,000 行，CTE 理货表会更快，但在大约 10K 标记之后，标准理货表解决方案会变得更好。CLR 字符串拆分模块会比两者都快，但我知道很多商店都对安装 CLR 模块犹豫不决。

    • 0

相关问题