--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([Cid] [nvarchar](10),[费用1] [int],[费用2] [int],[费用3] [int],[费用4] [int]) INSERT INTO [tb] SELECT '001','1000','2000','3000','4000'-->SQL查询如下:SELECT Cid,费用价格 FROM [tb] UNPIVOT(费用价格 for 类型 in([费用1],[费用2],[费用3],[费用4]))b /* Cid 费用价格 ---------- ----------- 001 1000 001 2000 001 3000 001 4000(4 行受影响) */
Create table Test1( cid nvarchar(20)',' fee int)insert into test1(cid,fee) select '001','1000' union select '001','2000' union select '001','3000' union select '001','4000'select cid,[1000] as '费用1',[2000] as '费用2',[3000] as '费用3',[4000] as '费用4' from (select cid,fee,fee as f from test1 ) a pivot (max(fee) for f in ([1000],[2000],[3000],[4000])) as pvt你要的代码是这个,这个就能达到你要的效果了 行列转换你可以看下这个地址的例子,里面很详细了 http://topic.csdn.net/u/20100312/00/9CF39500-8210-4E0A-AB97-FC3C4189A5FC.html
SELECT Cid,费用价格 FROM [tb] UNPIVOT(费用价格 for 类型 in([费用1],[费用2],[费用3],[费用4]))b这个代码应该这个
if OBJECT_ID('tb') is not null drop table tb; go create table tb ( cid varchar(5), 费用1 int, 费用2 int, 费用3 int, 费用4 int ); go insert tb select '001',1000,2000,3000,4000 go select Cid,费用价格 from (select * from tb) a unpivot( val for 费用价格 in (费用1,费用2,费用3,费用4)) b /* Cid 费用价格 ----- -------------------------------------------------------------------------------------------------------------------------------- 001 费用1 001 费用2 001 费用3 001 费用4(4 行受影响)*/
if OBJECT_ID('tb') is not null drop table tb; go create table tb ( cid varchar(5), 费用1 int, 费用2 int, 费用3 int, 费用4 int ); go insert tb select '001',1000,2000,3000,4000 go select Cid, 费用价格 from (select * from tb) a unpivot( 费用价格 for val in (费用1,费用2,费用3,费用4)) b /* Cid 费用价格 ----- ----------- 001 1000 001 2000 001 3000 001 4000(4 行受影响)*/
select cid, 费用1 as 费用价格 from yourTable union select cid, 费用2 as 费用价格 from yourTable union select cid, 费用3 as 费用价格 from yourTable union select cid, 费用4 as 费用价格 from yourTable
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Cid] [nvarchar](10),[费用1] [int],[费用2] [int],[费用3] [int],[费用4] [int])
INSERT INTO [tb]
SELECT '001','1000','2000','3000','4000'-->SQL查询如下:SELECT Cid,费用价格 FROM [tb] UNPIVOT(费用价格 for 类型 in([费用1],[费用2],[费用3],[费用4]))b
/*
Cid 费用价格
---------- -----------
001 1000
001 2000
001 3000
001 4000(4 行受影响)
*/
cid nvarchar(20)','
fee int)insert into test1(cid,fee)
select '001','1000'
union
select '001','2000'
union
select '001','3000'
union
select '001','4000'select cid,[1000] as '费用1',[2000] as '费用2',[3000] as '费用3',[4000] as '费用4' from
(select cid,fee,fee as f from test1 ) a pivot (max(fee) for f in ([1000],[2000],[3000],[4000])) as pvt你要的代码是这个,这个就能达到你要的效果了
行列转换你可以看下这个地址的例子,里面很详细了
http://topic.csdn.net/u/20100312/00/9CF39500-8210-4E0A-AB97-FC3C4189A5FC.html
if OBJECT_ID('tb') is not null drop table tb;
go
create table tb (
cid varchar(5),
费用1 int,
费用2 int,
费用3 int,
费用4 int );
go
insert tb select '001',1000,2000,3000,4000
go
select Cid,费用价格 from
(select * from tb) a
unpivot( val for 费用价格 in (费用1,费用2,费用3,费用4)) b
/*
Cid 费用价格
----- --------------------------------------------------------------------------------------------------------------------------------
001 费用1
001 费用2
001 费用3
001 费用4(4 行受影响)*/
if OBJECT_ID('tb') is not null drop table tb;
go
create table tb (
cid varchar(5),
费用1 int,
费用2 int,
费用3 int,
费用4 int );
go
insert tb select '001',1000,2000,3000,4000
go
select Cid, 费用价格 from
(select * from tb) a
unpivot( 费用价格 for val in (费用1,费用2,费用3,费用4)) b
/*
Cid 费用价格
----- -----------
001 1000
001 2000
001 3000
001 4000(4 行受影响)*/
select cid, 费用1 as 费用价格 from yourTable
union
select cid, 费用2 as 费用价格 from yourTable
union
select cid, 费用3 as 费用价格 from yourTable
union
select cid, 费用4 as 费用价格 from yourTable