如何遍历多列以在 SQL Server 中搜索值 [英] how to iterate through multiple columns to search for a value in SQL server

问题描述

我有如下表格

CREATE TABLE [dbo].[SAMPLE](
[COL_01] [nvarchar](50) NULL,
[COL_02] [nvarchar](50) NULL,
[COL_03] [nvarchar](50) NULL,
[COL_04] [nvarchar](50) NULL,
[COL_05] [nvarchar](50) NULL,
[COL_06] [nvarchar](50) NULL,
[COL_07] [nvarchar](50) NULL,
[COL_08] [nvarchar](50) NULL,
[COL_09] [nvarchar](50) NULL,
[COL_10] [nvarchar](50) NULL,
[COL_11] [nvarchar](50) NULL,
[COL_12] [nvarchar](50) NULL,
[COL_13] [nvarchar](50) NULL,
[COL_14] [nvarchar](50) NULL,
[COL_15] [nvarchar](50) NULL,
[COL_16] [nvarchar](50) NULL,
[COL_17] [nvarchar](50) NULL,
[COL_18] [nvarchar](50) NULL
) ON [PRIMARY]

我想在这些列的每一列中搜索像未知"这样的值.如何遍历这 18 列以找到值未知".目前我使用类似下面的东西来生成一个 SQL，但想知道是否有游标或存储过程可以将它包装在

I want to search for a value like 'Unknown' in each of these columns. how do iterate through these 18 columns to find a value 'Unknown'. Currently I use something like below to generate a SQL but was wondering if there is cursor or stored procedure that can wrap this in

SELECT 'Select * from SAMPLE where '+ c.name + ' = ''Unknown'''
FROM sysobjects o
INNER JOIN syscolumns c ON c.id = o.id
INNER JOIN systypes t ON t.xusertype = c.xusertype
WHERE o.name = 'SAMPLE'
AND C.name like ( 'COL_%' )

谢谢

推荐答案

您可以对结果进行逆透视，然后与 'Unknown' 进行比较.这是一种方式(对于 SQL Server 2008+):

You can unpivot your results and then compare against 'Unknown'. This is one way (for SQL Server 2008+):

SELECT
  x.Col,
  x.Value
FROM dbo.[SAMPLE] t
CROSS APPLY 
(
    VALUES
        ('COL_01', t.COL_01),
        ('COL_02', t.COL_02),
        ('COL_03', t.COL_03),
        ('COL_04', t.COL_04),
        ('COL_05', t.COL_05),
        ('COL_06', t.COL_06),
        ('COL_07', t.COL_07),
        ('COL_08', t.COL_08),
        ('COL_09', t.COL_09),
        ('COL_10', t.COL_10),
        ('COL_11', t.COL_11),
        ('COL_12', t.COL_12),
        ('COL_13', t.COL_13),
        ('COL_14', t.COL_14),
        ('COL_15', t.COL_15),
        ('COL_16', t.COL_16),
        ('COL_17', t.COL_17),
        ('COL_18', t.COL_18)
) x (Col, Value)
WHERE X.Value = 'Unknown';

对于 SQL Server 2005+，您可以使用上述变体，其中 VALUES ... 语法被替换为一系列通过 UNION ALL 组合的 SELECT:

For SQL Server 2005+, you can use the variation of the above where the VALUES ... syntax is replaced with a series of SELECTs combined via UNION ALL:

SELECT
  x.Col,
  x.Value
FROM dbo.[SAMPLE] t
CROSS APPLY 
(
    SELECT 'COL_01', t.COL_01 UNION ALL
    SELECT 'COL_02', t.COL_02 UNION ALL
    SELECT 'COL_03', t.COL_03 UNION ALL
    SELECT 'COL_04', t.COL_04 UNION ALL
    SELECT 'COL_05', t.COL_05 UNION ALL
    SELECT 'COL_06', t.COL_06 UNION ALL
    SELECT 'COL_07', t.COL_07 UNION ALL
    SELECT 'COL_08', t.COL_08 UNION ALL
    SELECT 'COL_09', t.COL_09 UNION ALL
    SELECT 'COL_10', t.COL_10 UNION ALL
    SELECT 'COL_11', t.COL_11 UNION ALL
    SELECT 'COL_12', t.COL_12 UNION ALL
    SELECT 'COL_13', t.COL_13 UNION ALL
    SELECT 'COL_14', t.COL_14 UNION ALL
    SELECT 'COL_15', t.COL_15 UNION ALL
    SELECT 'COL_16', t.COL_16 UNION ALL
    SELECT 'COL_17', t.COL_17 UNION ALL
    SELECT 'COL_18', t.COL_18
) x (Col, Value)
WHERE X.Value = 'Unknown';

或者你可以使用UNPIVOT:

SELECT *
FROM dbo.[SAMPLE] AS t
UNPIVOT(Value FOR Col IN (COL_01,COL_02,COL_03,COL_04.....COL_18)) AS x
WHERE x.Value = 'Unknown';

这篇关于如何遍历多列以在 SQL Server 中搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！