Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
HI ,
 
I have a table containing
 
Columns --> d1 d2 d3 d4 .......
 
Values --> HL P WO HL ............
 
TABLE VALUES MAY VARY .BUT I HAVE A STRING HL
 
how to compare a string value to all the columns in a single table .and get column count dynamically
 
FOR EX: FOR HL STRING COLUMN COUNT IS 2.
 
HOW TO GET THIS DYNAMICALLY
Posted 10-Jul-13 2:27am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

pls try this
 
replace the 'yourCheckString' with the substring you are searching in the tables, here this script will check all the tables for the substring and retruns the column count which contains the matched string.
 
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
DECLARE @BuildCondition VARCHAR(200)
DECLARE @BuildSpecialChars VARCHAR(200)
DECLARE @CheckSubstring VARCHAR(200)
DECLARE @sql VARCHAR(500)
 
SET @CheckSubstring='yourCheckString';
SET @BuildSpecialChars ='PATINDEX(''%'+@CheckSubstring+'%'',[somecolumn])>0'
 
if OBJECT_ID('tempdb..#t')is not null
  Drop table #t
  
CREATE TABLE #t (
    tablename VARCHAR(64),
    columnname VARCHAR(64),
    specialchar nvarchar(64)
)
 
DECLARE TempTables CURSOR
FOR
	SELECT o.name, c.name
	FROM syscolumns c
		INNER JOIN sysobjects o ON c.id = o.id
	WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
	ORDER BY o.name, c.name
OPEN TempTables
FETCH NEXT FROM TempTables
INTO @table, @column
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @BuildCondition='';
			SET @BuildCondition=REPLACE(@BuildSpecialChars,'somecolumn',@column);
 
			SET @sql = 'IF EXISTS(SELECT NULL FROM ' + @table + ' '
			SET @sql = @sql + 'WHERE '+@BuildCondition+') '
			SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
			SET @sql = @sql + @column + ''','''
			SET @sql = @sql + ''')'
			EXEC(@sql)
		
		FETCH NEXT FROM TempTables
		INTO @table, @column
	END
CLOSE TempTables
DEALLOCATE TempTables
 
SELECT count(*) FROM #t
 
hope this will help...
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

try this will work...
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
declare @qry as nvarchar(max);
select @cols = STUFF((SELECT distinct 
           ' sum (case when ' + column_name+'='+''''+'HL'+''''+'then 1 else 0 end)+'
               FROM information_schema.columns where table_name='YourTableName'
               FOR XML PATH(''), TYPE
               ).value('.', 'NVARCHAR(MAX)') 
               ,1,1,'')
SET @cols = LEFT(@cols, LEN(@cols) - 1)
set @qry='select '+ @cols + ' as Column_Count from YourTableName'
execute(@qry);
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 410
1 Sergey Alexandrovich Kryukov 329
2 Afzaal Ahmad Zeeshan 264
3 BillWoodruff 235
4 CPallini 195
0 OriginalGriff 5,560
1 DamithSL 4,476
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 10 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100