Question

i 有必要将单一行文的所有价值,而不是否定的价值观,如:

table:

CustomerName  Address Zip
Alex          Moscow  1234

结果:

CustomerName: Alex
Address: Moscow
Zip: 1234

重要的说明——即不了解外地名称/类型,因此,它应当贯穿所有领域,而且所有价值都不会增加。

Looks like it can do this using xquery, but can t find right syntax. Any hints?

感谢!

Answer 1

select T2.N.value( local-name(.) ,  nvarchar(128) )+ :  +
       T2.N.value( . ,  nvarchar(max) )
from (select *
      from YourTable
      for xml path(  ), type) as T1(X)
  cross apply T1.X.nodes( /* ) as T2(N)

Answer 2

select  CustomerName:   + isNull(CustometName,   ) +  Address:   
+ isNull(Address,   ), +  Zip:  + isNull(Zip,   ) from [TableName]

也许你们需要把一些价值观带给var。

Answer 3

不是作为Mikael的解决办法。但是,我仍然希望列入。

DECLARE @yourtable nvarchar(128)
DECLARE @sql as nvarchar(2100)
DECLARE @col as nvarchar(2000)

SET @yourtable =  <tablename> 

SELECT @col = coalesce(@col,    ) +  +   +t2.column_name+ :    + cast([  + t2.column_name +  ] as varchar) + char(32) 
FROM INFORMATION_SCHEMA.TABLES t1 join 
INFORMATION_SCHEMA.COLUMNS t2 on t1.table_name = t2.table_name
where t2.is_nullable =  NO  and t1.table_name = @yourtable
and t1.table_type =  BASE TABLE  and t1.table_schema = t2.table_schema
and t2.table_schema =  dbo 
SET  @sql =  select   + stuff(@col, 1,1,  ) +  from   + @yourtable
EXEC (@sql)

友情链接