行转列

[weixin_36732892]

我想在数据库里把一行数据变成两列，一列是列名，一列是对应的值。求解怎么对应上呢。两个单独输出我知道。但是列名与数据对应就不知道怎么弄了。
例：

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([code] nvarchar(50),[name] nvarchar(25),[value] nvarchar(21),[isdel] int,[Description] nvarchar(50),[createOn] nvarchar(50))

insert into #T values(newid(),N'Name_s',N'Values',0,'描述描述描述','admin');

select * from #T

--需要得到的效果
if not object_id(N'Tempdb..#Tables') is null
    drop table #Tables
Go
Create table #Tables([name] nvarchar(50),[value] nvarchar(50))

insert into #Tables values(N'code',N'8DCC50F4-450B-488D-8562-A0E48720B3AB');
insert into #Tables values(N'name',N'Name_s');
insert into #Tables values(N'value',N'Values');
insert into #Tables values(N'isdel',N'0');
insert into #Tables values(N'Description',N'描述描述描述');
insert into #Tables values(N'createOn',N'admin');

select * from #Tables

[「已注销」]

刚自己改了改，好使了。有更好的么？

if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([date] datetime,[zt_bll] int,[zt_mbz] int,[xz_bll] int,[xz_mbz] int,[hck_bll] int,[hck_mbz] int,[dg_bll] int,[dg_mbz] int,[jd_bll] int,[jd_mbz] int,[mk_bll] int,[mk_mbz] int,[bm_bll] int,[bm_mbz] int,[dc_bll] int,[dc_mbz] int,[mt_bll] int,[mt_mbz] int,[fj_bll] int,[fj_mbz] int,[a_bll] int,[a_mbz] int,[b_bll] int,[b_mbz] int,[mkl_bll] int,[mkl_mbz] int,[bml_bll] int,[bml_mbz] int,[dcl_bll] int,[dcl_mbz] int,[mtl_bll] int,[mtl_mbz] int,[fjl_bll] int,[fjl_mbz] int,[qcl_bll] int,[qcl_mbz] int,[tpv_bll] int,[tpv_mbz] int,[gj_bll] varchar(3),[gj_mbz] varchar(3),[ycl_bll] varchar(3),[ycl_mbz] varchar(3),[wg_bll] varchar(3),[wg_mbz] varchar(3),[gc_bll] varchar(3),[gc_mbz] varchar(3),[jz_scsl] int,[jz_blsl] int,[jz_bll] varchar(3),[jz_mbz] varchar(3),[mft_scsl] int,[mft_blsl] int,[mft_bll] int,[mft_mbz] int,[jzzj_jypc] int,[jzzj_blpc] int,[jzzj_bll] int,[jzzj_mbz] int,[jh_jypc] int,[jh_blpc] int,[jh_bll] int,[jh_mbz] int,[tpv_jypc] int,[tpv_blpc] int,[tpv1_bll] int,[tpv1_mbz] int,[bf_qcl] int,[bf_lh] int,[bf_jc] int,[bf_jh] int,[zxs_wtgj] int,[dq_wtgj] int,[aq_wtgj] int,[zxs_wtyf] int,[dq_wtyf] int,[aq_wtyf] int,[zxs_jytl] int,[dq_jytl] int,[aq_jytl] int,[zxs_glxt] int,[dq_glxt] int,[aq_glxt] int)
insert [huang]
select '2014-07-11',0,876,8130,76,97560,8766,1731707,0,170731,4,45935,2,432692,852,1024390,85285,19965,2852,1097560,855,1000000,3,1000000,123,0,745,0,575,0,75,0,575,0,76,0,74,0,74,'90%','90%','90%','90%','90%','90%','90%','90%',12312,123,'12%','12%',123,12,3123,123,123123,123,12,213,213,123,123,213,123,1123,23,123,123,123,123,123,123,1,2312,123,23,3123,12,123,123,3,1231,123
--------------开始查询--------------------------
 
declare @s nvarchar(max)
select @s=isnull(@s+' union all ','')+'select Name='''+Name+''', [col]=cast('+quotename(Name)+' as varchar(10)) '--isnull(@s+' union all ','') 去掉字符串@s中第一个union all+',[Score]='+quotename(name)
+' from [huang]'
from syscolumns where ID=object_id('[huang]') --and Name not in('Student')--排除不转换的列
order by Colid

print @s
EXEC  ('select * from ('+@s+')t ')--增加一个排序

[听雨停了]

引用 1 楼 weixin_36732892 的回复:

刚自己改了改，好使了。有更好的么？

if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([date] datetime,[zt_bll] int,[zt_mbz] int,[xz_bll] int,[xz_mbz] int,[hck_bll] int,[hck_mbz] int,[dg_bll] int,[dg_mbz] int,[jd_bll] int,[jd_mbz] int,[mk_bll] int,[mk_mbz] int,[bm_bll] int,[bm_mbz] int,[dc_bll] int,[dc_mbz] int,[mt_bll] int,[mt_mbz] int,[fj_bll] int,[fj_mbz] int,[a_bll] int,[a_mbz] int,[b_bll] int,[b_mbz] int,[mkl_bll] int,[mkl_mbz] int,[bml_bll] int,[bml_mbz] int,[dcl_bll] int,[dcl_mbz] int,[mtl_bll] int,[mtl_mbz] int,[fjl_bll] int,[fjl_mbz] int,[qcl_bll] int,[qcl_mbz] int,[tpv_bll] int,[tpv_mbz] int,[gj_bll] varchar(3),[gj_mbz] varchar(3),[ycl_bll] varchar(3),[ycl_mbz] varchar(3),[wg_bll] varchar(3),[wg_mbz] varchar(3),[gc_bll] varchar(3),[gc_mbz] varchar(3),[jz_scsl] int,[jz_blsl] int,[jz_bll] varchar(3),[jz_mbz] varchar(3),[mft_scsl] int,[mft_blsl] int,[mft_bll] int,[mft_mbz] int,[jzzj_jypc] int,[jzzj_blpc] int,[jzzj_bll] int,[jzzj_mbz] int,[jh_jypc] int,[jh_blpc] int,[jh_bll] int,[jh_mbz] int,[tpv_jypc] int,[tpv_blpc] int,[tpv1_bll] int,[tpv1_mbz] int,[bf_qcl] int,[bf_lh] int,[bf_jc] int,[bf_jh] int,[zxs_wtgj] int,[dq_wtgj] int,[aq_wtgj] int,[zxs_wtyf] int,[dq_wtyf] int,[aq_wtyf] int,[zxs_jytl] int,[dq_jytl] int,[aq_jytl] int,[zxs_glxt] int,[dq_glxt] int,[aq_glxt] int)
insert [huang]
select '2014-07-11',0,876,8130,76,97560,8766,1731707,0,170731,4,45935,2,432692,852,1024390,85285,19965,2852,1097560,855,1000000,3,1000000,123,0,745,0,575,0,75,0,575,0,76,0,74,0,74,'90%','90%','90%','90%','90%','90%','90%','90%',12312,123,'12%','12%',123,12,3123,123,123123,123,12,213,213,123,123,213,123,1123,23,123,123,123,123,123,123,1,2312,123,23,3123,12,123,123,3,1231,123
--------------开始查询--------------------------
 
declare @s nvarchar(max)
select @s=isnull(@s+' union all ','')+'select Name='''+Name+''', [col]=cast('+quotename(Name)+' as varchar(10)) '--isnull(@s+' union all ','') 去掉字符串@s中第一个union all+',[Score]='+quotename(name)
+' from [huang]'
from syscolumns where ID=object_id('[huang]') --and Name not in('Student')--排除不转换的列
order by Colid

print @s
EXEC  ('select * from ('+@s+')t ')--增加一个排序

可以这样列转行显示

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([code] nvarchar(50),[name] nvarchar(25),[value] nvarchar(21),[isdel] int,[Description] nvarchar(50),[createOn] nvarchar(50))
insert into #T values(newid(),N'Name_s',N'Values',0,'描述描述描述','admin');
--测试数据结束

--把数据插入一个实体表中，因为临时表在syscolumns表中找不到列名
IF OBJECT_ID('tb') IS NOT NULL
	DROP TABLE tb
SELECT * INTO Tb from #T

--开始转换过程
DECLARE @sql             NVARCHAR(MAX),
        @name            NVARCHAR(MAX),
        @name_noCast     NVARCHAR(200)
SET @name = STUFF(
        (
            SELECT ',cast([' + NAME + '] as nvarchar(200)) as ' + NAME
            FROM   syscolumns
            WHERE  ID = OBJECT_ID('tb')
            ORDER BY
                   Colid
                   FOR XML PATH('')
        ),
        1,
        1,
        ''
    )

SET @name_noCast = STUFF(
        (
            SELECT ',[' + NAME + ']'
            FROM   syscolumns
            WHERE  ID = OBJECT_ID('tb')
            ORDER BY
                   Colid
                   FOR XML PATH('')
        ),
        1,
        1,
        ''
    )

SET @sql = 'select [names],[value_s] from (select ' + @name + 
    ' from tb) a unpivot ([value_s] for [names] in(' + @name_noCast + '))b'

EXEC (@sql)

[道素]

declare @cols nvarchar(max) ,@sql nvarchar(max)
select @cols=ISNULL(@cols+N',(',N'(')+''''+c.name+N''',CONVERT(NVARCHAR(MAX),'+QUOTENAME(c.name)+'))' from tempdb.sys.columns as c where c.object_id=object_id(N'tempdb..#t')
SET @sql=N'
select c.col,c.val from #t
cross apply(values'+@cols+')c(col,val)'
PRINT @sql
EXEC(@sql)

col	val
code	C117F8E2-7159-4C1C-9EF7-6E484DF7DC9C
name	Name_s
value	Values
isdel	0
Description	描述描述描述
createOn	admin