求条非常简单的SQL语句。。。。。。。。

Question

这个是表。
sqlserver2005
id        name       a1        a2         a3
1          ad        001       002        003
想要结果id        name       lname        lvalue       
1          ad         a1            001
2          ad         a2            002
3          ad         a3            003

Answer 1

莫非是use ProduceManageSystem;
go
if object_id(N'A',N'U') is not null drop table A
go
create table A
(
  id int,
  [name] nvarchar(10),
  a1 nvarchar(10),
  a2 nvarchar(10),
  a3 nvarchar(10)
)
go
insert into A
select 1,'ad','001','002','003'
go
with cte as
(
select * from A,(select  top 3 row_number()over(order by getdate()) as RN from sysobjects) as B
)
select id,[name],
case when RN=1 then 'a1'
     when RN=2 then 'a2'
     when RN=3 then 'a3'
     else '' end as lname,
case when RN=1 then a1
     when RN=2 then a2
     when RN=3 then a3
     else '' end as lvalue
from cte
/*
(1 行受影响)
id          name       lname lvalue
----------- ---------- ----- ----------
1           ad         a1    001
1           ad         a2    002
1           ad         a3    003(3 行受影响)
*/

Answer 2

use ProduceManageSystem;
go
if object_id(N'A',N'U') is not null drop table A
go
create table A
(
  id int,
  [name] nvarchar(10),
  a1 nvarchar(10),
  a2 nvarchar(10),
  a3 nvarchar(10)
)
go
insert into A
select 1,'ad','001','002','003'
go
with cte as
(
select * from A,(select  top 3 row_number()over(order by getdate()) as RN from sysobjects) as B
)
select id,[name],
case when RN=1 then 'a1'
     when RN=2 then 'a2'
     when RN=3 then 'a3'
     else '' end as lname,
case when RN=1 then a1
     when RN=2 then a2
     when RN=3 then a3
     else '' end as lvalue
from cte
/*
(1 行受影响)
id          name       lname lvalue
----------- ---------- ----- ----------
1           ad         a1    001
1           ad         a2    002
1           ad         a3    003(3 行受影响)
*/

Answer 3

如果是只有3列，写成静态的即可select id,name,'a1' as lname,a1 as lvalue from tb
union all
select id,name,'a2' as lname,a2 as lvalue from tb
union all
select id,name,'a3' as lname,a3 as lvalue from tb

Answer 4

莫非是use ProduceManageSystem;
go
if object_id(N'A',N'U') is not null drop table A
go
create table A
(
  id int,
  [name] nvarchar(10),
  a1 nvarchar(10),
  a2 nvarchar(10),
  a3 nvarchar(10)
)
go
insert into A
select 1,'ad','001','002','003'
go
with cte as
(
select * from A,(select  top 3 row_number()over(order by getdate()) as RN from sysobjects) as B
)
select RN as id,[name],
case when RN=1 then 'a1'
     when RN=2 then 'a2'
     when RN=3 then 'a3'
     else '' end as lname,
case when RN=1 then a1
     when RN=2 then a2
     when RN=3 then a3
     else '' end as lvalue
from cte
/*
(1 行受影响)
id                   name       lname lvalue
-------------------- ---------- ----- ----------
1                    ad         a1    001
2                    ad         a2    002
3                    ad         a3    003(3 行受影响)
*/

Answer 5

如果有多列，需要借助sys.columns表用动态SQL
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(2),[a1] varchar(3),[a2] varchar(3),[a3] varchar(3))
insert [tb]
select 1,'ad','001','002','003'
 
---查询---
declare @sql varchar(8000)
select 
  @sql=isnull(@sql+' union all ','')+' select id,name,'''+name+''' as lname,'+name+' from tb'
from
  sys.columns 
where 
  object_id=object_id('tb') 
and
  name not in('id','name')
order by 
  column_id
--print @sql
exec (@sql)---结果---
id          name lname a1
----------- ---- ----- ----
1           ad   a1    001
1           ad   a2    002
1           ad   a3    003(3 行受影响)

Answer 6

修改最后一个列名
declare @sql varchar(8000)
select 
  @sql=isnull(@sql+' union all ','')+' select id,name,'''+name+''' as lname,'+name+' as lvalue from tb'
from
  sys.columns 
where 
  object_id=object_id('tb') 
and
  name not in('id','name')
order by 
  column_id
--print @sql
exec (@sql)/**
id          name lname lvalue
----------- ---- ----- ------
1           ad   a1    001
1           ad   a2    002
1           ad   a3    003(3 行受影响)
**/

Answer 7

可以看看unpivot。
select *
from table
unpivot
(
lvalue for lname in ([a1],[a2],[a3])
)up如果是多列的话，把后面的a1，a2,a3变成动态的。从sys.columns里面取