动态地将值与表中的所有列进行比较,并返回sql中匹配的列数 [英] dynamically compare value to all columns in a table and return matched column count in sql
本文介绍了动态地将值与表中的所有列进行比较,并返回sql中匹配的列数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
HI,
我有一张桌子包含
列 - > d1 d2 d3 d4 .......
值 - > HL P WO HL ............
表值可能不一样。但我有一个STRING HL
如何将字符串值与单个表中的所有列进行比较。并动态获取列数
FOR EX:FOR HL STRING COLUMN COUNT是2.
如何获得动态
解决方案
请试试这个
将'yourCheckString'替换为您在表中搜索的子字符串,此处此脚本将检查子字符串的所有表,并重新计算包含匹配字符串的列计数。
DECLARE @表 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')是 不 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
< span class =code-keyword> 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 温泉n> @sql = @ sql + @ column + ' '','''
SET @ sql = @ sql + ' ''''
EXEC ( @sql )
FETCH NEXT FROM TempTables
INTO @ table , @ column
END
CLOSE TempTables
DEALLOCATE TempTables
SELECT count(*)来自 #t
希望这会有所帮助......
尝试这将工作...
DECLARE @ cols AS NVARCHAR (MAX), @查询 AS NVARCHAR (MAX);
声明 @ qry as nvarchar (max);
选择 @cols = STUFF(( SELECT distinct
' sum( ' + column_name + ' =' + ' ''' + ' HL ' + ' ''' + ' 然后1其他0结束)+'
FROM information_schema.columns 其中 table_name = ' YourTableName'
FOR XML PATH(' '), TYPE
).value(' 。',' NVARCHAR(MAX)')
, 1 , 1 ,' ')
SET @cols = LEFT ( @cols ,LEN( @cols ) - 1 )
set @ qry = < span class =code-string>' select' + @cols + ' 作为YourTableName的Column_Count'
执行( @ QRY 跨度>);
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
解决方案
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...
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);
这篇关于动态地将值与表中的所有列进行比较,并返回sql中匹配的列数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文