Click here to Skip to main content
11,576,993 members (58,841 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 221
1 DamithSL 155
2 Abhinav S 130
3 OriginalGriff 95
4 Lucifier Rocks 85
0 OriginalGriff 715
1 Sergey Alexandrovich Kryukov 631
2 Abhinav S 528
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 365


Advertise | Privacy | Mobile
Web04 | 2.8.150603.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