Click here to Skip to main content
11,720,364 members (70,487 online)
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 1: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 Sergey Alexandrovich Kryukov 2,029
1 OriginalGriff 844
2 F-ES Sitecore 640
3 Maciej Los 609
4 Richard MacCutchan 540


Advertise | Privacy | Mobile
Web01 | 2.8.150901.1 | Last Updated 10 Jul 2013
Copyright © CodeProject, 1999-2015
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