有点头大!帮忙看看如何写这个语句~拜谢各位大侠!!
表(a b c 是字段名)
a b c
1 1 1
结果集
a 1
b 1
c 1
要怎么把表行改列,列改行?
[解决办法]
select a union all
select b union all
select c
?
[解决办法]
動態?
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select '+quotename(Name,'''')+', '+quotename(Name,'''')
+'='+quotename(Name) +' from tb '
from syscolumns where ID=object_id('tb')
exec (@s)
if object_id('tb') is not null
drop table tb
go
create table tb(a int,b int,c int)
insert tb
values(1,1,1)
select 'a' as name,a from tb
union all
select 'b' as name,b from tb
union all
select 'c' as name,c from tb
/*
namea
a 1
b 1
c 1
*/
if object_id('tb') is not null
drop table tb
go
create table tb(a int,b int,c int)
insert tb
values(1,1,1)
select e,n
from tb
unpivot
(
n for e in (a,b,c)
)t
select e,n
from tb
unpivot
(
n for e in (a,b,c)
)t
/*
en
a1
b1
c1
*/
create table 表
(a int,b int,c int)
insert into 表(a,b,c)
select 1,1,1
select col,val
from 表 a
unpivot(val for col in(a,b,c)) p
/*
col val
------------- -------------
a 1
b 1
c 1
(3 row(s) affected)
*/
create table #tb (a int,b int,c int)
insert into #tb
select 1,1,1
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select '+quotename(Name,'''')+' as 列名, '+quotename(Name,'''')
+'='+quotename(Name) +' from #tb '
from tempdb .sys. syscolumns where ID=object_id(N'tempdb.dbo.#tb')
exec (@s)
drop table #tb
要动态的撒!
drop table tb
create table tb (a int,b int,c int)
insert into tb
select 1,1,1
declare @s nvarchar(4000)
set @s = '';
select @s= @s + ',['+name+']'
from sys.columns
where object_id = object_id('tb')
set @s = 'select col_name,col_value from tb unpivot(col_value for col_name in (' +
stuff(@s,1,1,'') +'))t'
--select @s
/*
select col_name,col_value from tb unpivot(col_value for col_name in ([a],[b],[c]))t
*/
exec (@s)
/*
col_namecol_value
a1
b1
c1
*/
----------------------------------------------------------------
-- Author :DBA_tbzj(發糞塗牆)
-- Date :2013-11-08 10:08:57
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(4),[b] varchar(1),[c] varchar(1))
insert [tb]
select '1','1','1' union all
select '哈哈','a','b'
--------------开始查询--------------------------
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select '+quotename(Name,'''') +'as name, '+quotename(Name,'''')
+'='+quotename(Name) +' from tb '
from syscolumns where ID=object_id('tb')
exec (@s)
----------------结果----------------------------
/*
name a
---- ----
a 1
a 哈哈
b 1
b a
c 1
c b
*/