sql - 仅当字符串长度大于 2 时才选择列

Question

类似的问题可能会被问到，但我找不到适合我需要的任何东西。

如何只选择字符串长度大于 2 的列

这是已经做了多少。

SELECT * FROM Table1

WHERE (Table1.ID = @ID)

或者类似的东西

WHERE (Table1.ID = @ID) AND (LEN(*) > 2)

感谢大家的帮助

我有一个表，其中有 35 列和一个用户 ID 列，现在我只想从那些具有 > 2 个字符串的列中选择和显示信息。

我喜欢只选择具有 > 2 个字符串和用户定义的 ID 的列，而不是整行!!

我希望我说得有道理。

表格

期望的结果

Answer 1

巨型编辑

虽然我同意@Joro 的方法，但我意识到还有一种稍微冗长但更简单的方法。

我创建了您的表的副本并将其命名为 Lessons，但我只在其中放入了 12 个 Lessons，但您可以用相同的方式生成查询。

使用以下查询(使用 INFORMATION_SCHEMA.COLUMNS ):

SELECT  'SELECT ID, ''' + COLUMN_NAME + ''' AS LessonName, 
        [' + COLUMN_NAME + '] AS Lesson ' +
+       'FROM Lesson WHERE ID = @ID AND LEN([' + COLUMN_NAME + ']) > 2 UNION' 

FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = 'Lesson'
AND DATA_TYPE = 'varchar'

我生成了一个如下所示的查询:

SELECT ID, 'Lesson 1' AS LessonName, [Lesson 1] AS Lesson  
FROM Lesson WHERE ID = @ID AND LEN([Lesson 1]) > 2 UNION
SELECT ID, 'Lesson 2' AS LessonName, [Lesson 2] AS Lesson  
FROM Lesson WHERE ID = @ID AND LEN([Lesson 2]) > 2 UNION
... (SQL omitted for brevity)
SELECT ID, 'Lesson 12' AS LessonName, [Lesson 12] AS Lesson  
FROM Lesson WHERE ID = @ID AND LEN([Lesson 12]) > 2 UNION

删除最后一个 UNION 并通过将 @ID 声明为 35 来运行查询得到:

|| ID || LessonName || Lesson
|| 35 || Lesson 4   || Maths
|| 35 || Lesson 9   || ICT
|| 35 || Lesson 12  || English

然后我心想，好吧，我可能只是使用上面的技术来旋转它......但后来我有另一个 - 我们真正想要的列在 LessonName 列中，所以，我们可能只运行一个具有这些列名称的动态 SQL 查询:

DECLARE @ColumnList VARCHAR(MAX)

SELECT @ColumnList = COALESCE(@ColumnList + ', ','') + '[' + Lessons.LessonName + ']'

FROM ( 
SELECT ID, 'Lesson 1' AS LessonName, [Lesson 1] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 1]) > 2 UNION
SELECT ID, 'Lesson 2' AS LessonName, [Lesson 2] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 2]) > 2 UNION
...
SELECT ID, 'Lesson 12' AS LessonName, [Lesson 12] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 12]) > 2) 

AS Lessons

这给了我结果 '[Lesson 4], [Lesson 9], [Lesson 12]'

反过来，您可以执行以下操作:

DECLARE @QuerySQL NVARCHAR(MAX)

SET @QuerySql = 'SELECT ' + CAST(@ID AS VARCHAR) + ' AS ID, ' + @ColumnList + ' FROM Lesson WHERE ID = @ID'

--Query actually looks like: SELECT 35 AS ID, [Lesson 4], [Lesson 9], [Lesson 12] 
--                           FROM Lesson WHERE ID = 35  

DECLARE @ID INT --You will already have done this above anyway really
SET @ID = 35    

EXEC sp_executeSQL @QuerySql,N'@ID int', @ID

哪个返回:

|| ID || Lesson 4 || Lesson 9 || Lesson 12 
|| 35 || Maths    || ICT      || English

使用数据透视函数的另一种方法 - 您可以轻松地生成此 sql 一次并将其留在存储过程中。

因此，总而言之，您的用法如下:

DECLARE @ID INT
SET @ID = 35

DECLARE @ColumnList VARCHAR(MAX)

SELECT @ColumnList = COALESCE(@ColumnList + ', ','') + '[' + Lessons.LessonName + ']' 

FROM (  
SELECT ID, 'Lesson 1' AS LessonName, [Lesson 1] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 1]) > 2 UNION 
SELECT ID, 'Lesson 2' AS LessonName, [Lesson 2] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 2]) > 2 UNION 
... 
SELECT ID, 'Lesson 35' AS LessonName, [Lesson 35] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 35]) > 2)  

AS Lessons --Remember you can generate this section quite simply using information_schema.columns
           --and you don't actually need the ID or Lesson columns - just the lesson names.

DECLARE @QuerySQL NVARCHAR(MAX) 

SET @QuerySql = 'SELECT ' + CAST(@ID AS VARCHAR) + ' AS ID, ' + @ColumnList + ' FROM Lesson WHERE ID = @ID'     

EXEC sp_executeSQL @QuerySql,N'@ID int', @ID 

这会给你想要的答案。

请注意，在 GUI 级别旋转数据(通过 Excel 数据透视表或像 DevExpress Pivot Grid 这样的第三方组件意味着您可以在第一个结果集 - UNION 查询的输出处停止)