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

[yinxiaoqi]

这个是表。
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

[王向飞]

我倒是觉得要是由2转成1是简单的，这个还真不简单。

[DataBox-MDX]

莫非是

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 行受影响)
*/

[DataBox-MDX]

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 行受影响)
*/

[百年树人]

如果是只有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

[DataBox-MDX]

莫非是

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 行受影响)
*/

[yinxiaoqi]

[Quote=引用 4 楼 josy 的回复:]
如果是只有3列，写成静态的即可


SQL code
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 fro……
[/Quote]

你好，如果列非常的多怎么办呢，

[百年树人]

[Quote=引用 6 楼 yinxiaoqi 的回复:]
引用 4 楼 josy 的回复:
如果是只有3列，写成静态的即可

SQL code
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 lnam……
[/Quote]
如果有多列，需要借助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 行受影响)

[百年树人]

修改最后一个列名

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 行受影响)
**/

[flygao85]

可以看看unpivot。
select *
from table
unpivot
(
lvalue for lname in ([a1],[a2],[a3])
)up

如果是多列的话，把后面的a1，a2,a3变成动态的。从sys.columns里面取

[yinxiaoqi]

[Quote=引用 8 楼 josy 的回复:]
修改最后一个列名

SQL code
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
obje……
[/Quote]

嗯，谢谢，树人大哥。。。。。