Question

我要做一个选择的语句。我要选择

SELECT COLUMN_NAME AS FieldName FROM   
INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME =  table1

However I want to create another column named Value which is a particular row in table1 so I have rows of the column name and the corresponding single value. Any thoughts on how to approach this?

Answer 1

下列查询为每列生成值( 最小值 ) :

    SELECT    select  +COLUMN_NAME+    AS FieldName, (select cast(MIN( +COLUMN_NAME+ ) as varchar(8000)) from  +const.tablename+ ) 
    FROM INFORMATION_SCHEMA.COLUMNS c cross join
         (select  AllCurveNames  as tablename) const
    WHERE c.TABLE_NAME = const.tablename

然而, 这将为每行生成一个单独的查询。要将它们合并起来, 您需要一个字符串聚合组合。您可以在 SQL 服务器中这样做 :

    declare @sql varchar(max);

    SELECT @sql = (select  select    +COLUMN_NAME+    AS FieldName, (select cast(MIN( +COLUMN_NAME+ ) as varchar(8000)) from  +const.tablename +  ) union all  
                   FROM INFORMATION_SCHEMA.COLUMNS c cross join
                        (select WHATEVER as tablename) const
                   WHERE c.TABLE_NAME = const.tablename
                   for xml path(  )
                  );
    select @sql = LEFT(@sql, len(@sql) - 9);
    exec(@sql);

Answer 2

使用交叉连结, 如果您只是从两个表格中选择没有连结( 即 < code> from t1, t2 ) 的表格, 则默认使用交叉连结 :

SELECT COLUMN_NAME AS FieldName,
       Table1.MyField
FROM
    INFORMATION_SCHEMA.COLUMNS, Table1
WHERE
    TABLE_NAME =  table1 
AND
    MyTable.ID = 123

Answer 3

我其实想出了一个疯狂的解决方案,但效果不错:

declare @tbl_name as varchar(255)
declare @field as varchar(255)
declare @val as varchar(255)
declare @SQL as nvarchar(4000)

create table #tbl ( [FieldName][varchar](255), [FieldVal][varchar](255))

set @tbl_name =  table1 

DECLARE mah_cursor CURSOR FAST_FORWARD 
FOR 
SELECT COLUMN_NAME FROM  
INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @tbl_name

OPEN mah_cursor

FETCH NEXT FROM mah_cursor INTO @field 

WHILE @@FETCH_STATUS = 0
BEGIN



set @SQL =  set @val = (Select top 1   + @field +   from   + @tbl_name +  )  
print @SQL


exec sp_executesql @query = @SQL, @params = N @val varchar(255) OUTPUT , @val = @val      OUTPUT

 insert into #tbl ([FieldName],[FieldVal] ) values (@field, @val)

 FETCH NEXT FROM mah_cursor INTO @field
 END

CLOSE mah_cursor 
DEALLOCATE mah_cursor 

select * from #tbl

drop table #tbl

它环绕每个值并添加它。 Fast_ Forward 特性优化高性能查询

友情链接