Jean 筆記本

 

使用方法：以下程式碼，把Table_Name全部取代為－資料庫實際的Table

 

DECLARE @Base TABLE(
    ColName VARCHAR(100),
    Val varchar(max)
)

DECLARE @col varchar(max)
DECLARE @cast varchar(max)

--取欄名 和值
    select TOP 1 *
    into #Table_Name
    from Table_Name WITH(NOLOCK)

    select @col=(select '['+c.name +'],' as [text()]
    from tempdb.sys.columns c join tempdb.sys.objects o
    on c.object_id =o.object_id
    where o.name like '%Table_Name%'
    for xml path(''))

    select @cast=(select 'cast(['+c.name +'] as nvarchar(max)) ['+ c.name +'],' as [text()]
    from tempdb.sys.columns c join tempdb.sys.objects o
    on c.object_id =o.object_id
    where o.name like '%Table_Name%'
    for xml path(''))

    declare @sql1 varchar(max)='select [ColName],[ColValue] from (select '+ left(@cast,len(@cast)-1)+ ' from #Table_Name) t
    UNPIVOT ([ColValue] for [ColName] in (' +LEFT(@col,len(@col)-1)+')) unpvt'

    insert into @Base(ColName ,Val)
    EXEC(@sql1)
    select * from @Base
    drop table #Table_Name