Select Columns Only if String length is greater than 2

Question

Similar Question may be asked but I am unable to find anything that fits my needs.

How can I select only columns where string length is greater than 2

This is how much has done yet.

SELECT * FROM Table1

WHERE (Table1.ID = @ID)

Or something like

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

Thank for all of your help

I have a Table, in which I have 35 columns and a User ID column, now I want to select and display information from only those columns which have > 2 string.

I Like to Select only columns which have > 2 string and the defined ID by User not the Whole Row !!

I hope I am making sense.

Table

Desired Result

Answer 1

GIANT EDIT

Although I agree with @Joro's approach, I realised there is a slightly more verbose but simpler way.

I created a copy of your table and called it Lessons, but I only put 12 Lessons in it, but you can generate your query in the same way.

Using the following query (which uses 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'

I generate a query that looks like:

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

Removing the last UNION and running the query by declaring @ID as 35 gives me:

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

I then thought to myself, well, I could probably just pivot this using the technique above... but then I had another though - the columns we actually want are in the LessonName column, so, we could probably just run a dynamic SQL query with those column names in:

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

Which gives me the result '[Lesson 4], [Lesson 9], [Lesson 12]'

Which, in turn, you could do the following with:

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

Which returns:

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

An alternative approach to using pivot functions - you can easily generate this sql once and leave it in a stored procedure.

So, to put this altogether, your usage would look like:

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 

Which will give you the answer you want.

Note that pivoting the data at the GUI level (either via an Excel Pivot table or a 3rd party component like the DevExpress Pivot Grid means you can stop at the first resultset - the output of the UNION queries)

Answer 2

I have not managed to to do this only with one dynamically build T-SQL statement. The following is what you should do in order to get the desire result:

Create temporary table to store the result:

DECLARE @DynamicSQLStatement NVARCHAR(MAX)

CREATE TABLE #DataSource
(
     [Id] BIGINT 
    ,[SubjectName] NVARCHAR(100)
    ,[Lesson] NVARCHAR(100)
)

Init the temporary table with specific data:

SET @DynamicSQLStatement=N' INSERT INTO #DataSource ([Id],[SubjectName],[Lesson])
                            SELECT   [Id]
                                    ,[SubjectName]
                                    ,[Lesson]
                            FROM SourceTable
                            UNPIVOT 
                            (
                                [SubjectName] FOR [Lesson] IN ('+(SELECT SUBSTRING((SELECT '],[' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns  WHERE TABLE_NAME = 'SourceTable' AND COLUMN_NAME<>'ID' FOR XML PATH('')),3,300)+']')+')
                            ) as pvt
                            WHERE ID=35 AND LEN([SubjectName])>2'

EXECUTE sp_executesql @DynamicSQLStatement

Now, we have these records in it:

Note the "WHERE" clause which limits the result. Also, 'SourceTable' should be replaced with the name of your table. What I am doing is to get all columns of this table without the "Id" one, but you can do this without sp_exetuesql as just writing down all your columns:

INSERT INTO #DataSource ([Id],[SubjectName],[Lesson])
SELECT   [Id]
        ,[SubjectName]
        ,[Lesson]
FROM SourceTable
UNPIVOT 
(
    [SubjectName] FOR [Lesson] IN ([Lesson 1],[Lesson 2],[Lesson 3],...,[Lesson n])
) as pvt
WHERE ID=35 AND LEN([SubjectName])>2'

And this is the last one - get the result in the desire format and drop the temp table:

SET @DynamicSQLStatement=N' SELECT *
                            FROM #DataSource
                            PIVOT
                            (
                                MAX([SubjectName]) FOR [Lesson] IN ('+ (SELECT SUBSTRING((SELECT '],[' + Lesson FROM #DataSource FOR XML PATH('')),3,100)+']')+')
                            )AS ResultTable'

EXECUTE sp_executesql @DynamicSQLStatement

DROP TABLE #DataSource

And this is the final result:

I hope this make sense to you. And I am really disappointed that I could not manage to do this with only one statement. If anyone can, it will be really interesting technique to be seen.

If I were you, I would make from this code store procedure with parameters the minimum length of the text in column and user id.