这个SELECT 语句如何写
这个SELECT 语句怎么写?
表名:Table1
字段：ID Cusno Price
s012 A 35.02
S012 C 38.6
...................
想得到结果是：
id Price Price
s012 35.02 38.6
请问这个select 语句怎么写？
------解决方案--------------------
不能在一个查询中存在两个Price吧
------解决方案--------------------
行列转换
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',max(case when Cusno= ' ' '+Cusno+ ' ' ' then Price else null end) as [ '+Cusno+ '] '
from table1
group by Cusno
exec( 'select ID '+@sql+ ' from table1 group by ID ')
------解决方案--------------------
create table T(ID nvarchar(10),Cusno nvarchar(10),Price decimal(10,2))
insert T select 's012 ', 'A ', 35.02
union all select 'S012 ', 'C ', 38.6
declare @sql nvarchar(2000)
set @sql= 'select ID, '
select @sql=@sql+ 'Price=sum(case when Cusno= ' ' '+Cusno+ ' ' ' then Price else 0 end), '
from T
group by Cusno
select @sql=left(@sql, len(@sql)-1),@sql=@sql+ ' from T group by ID '
exec(@sql)
--result
ID Price Price
---------- ---------------------------------------- ----------------------------------------
s012 35.02 38.60
------解决方案--------------------
--如果Cusno都是A C這樣的數據，且Cusno的數據不是固定的話，看看這樣的效果，樓主是否滿意
--創建測試環境
Create Table Table1
(ID Varchar(10),
Cusno Varchar(10),
Price Numeric(10, 2))
Insert Table1 Select 'S012 ', 'A ', 35.02
Union All Select 'S012 ', 'C ', 38.6
Union All Select 'S013 ', 'A ', 52.00
GO
--測試
--如果Cusno的數據是固定的
Select
ID,
SUM(Case Cusno When 'A ' Then Price Else 0.00 End) As PriceA,
SUM(Case Cusno When 'C ' Then Price Else 0.00 End) As PriceC
From
Table1
Group By
ID
--如果Cusno的數據不是固定的
Declare @S Varchar(8000)
Select @S = 'Select ID '
Select @S = @S + ', SUM(Case Cusno When ' ' ' + Cusno + ' ' ' Then Price Else 0.00 End) As Price '+ Cusno
From Table1 Group By Cusno
Select @S = @S + ' From Table1 Group By ID '
EXEC(@S)
GO
--刪除測試環境
Drop Table Table1
--結果
/*
ID PriceA PriceC
S012 35.02 38.60
S013 52.00 0.00
*/
------解决方案--------------------
向鱼老大学习，我也写个动态的：
declare @v varchar(1000)
set @v= 'select id '
表名:Table1
字段：ID Cusno Price
s012 A 35.02
S012 C 38.6
...................
想得到结果是：
id Price Price
s012 35.02 38.6
请问这个select 语句怎么写？
------解决方案--------------------
不能在一个查询中存在两个Price吧
------解决方案--------------------
行列转换
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',max(case when Cusno= ' ' '+Cusno+ ' ' ' then Price else null end) as [ '+Cusno+ '] '
from table1
group by Cusno
exec( 'select ID '+@sql+ ' from table1 group by ID ')
------解决方案--------------------
create table T(ID nvarchar(10),Cusno nvarchar(10),Price decimal(10,2))
insert T select 's012 ', 'A ', 35.02
union all select 'S012 ', 'C ', 38.6
declare @sql nvarchar(2000)
set @sql= 'select ID, '
select @sql=@sql+ 'Price=sum(case when Cusno= ' ' '+Cusno+ ' ' ' then Price else 0 end), '
from T
group by Cusno
select @sql=left(@sql, len(@sql)-1),@sql=@sql+ ' from T group by ID '
exec(@sql)
--result
ID Price Price
---------- ---------------------------------------- ----------------------------------------
s012 35.02 38.60
------解决方案--------------------
--如果Cusno都是A C這樣的數據，且Cusno的數據不是固定的話，看看這樣的效果，樓主是否滿意
--創建測試環境
Create Table Table1
(ID Varchar(10),
Cusno Varchar(10),
Price Numeric(10, 2))
Insert Table1 Select 'S012 ', 'A ', 35.02
Union All Select 'S012 ', 'C ', 38.6
Union All Select 'S013 ', 'A ', 52.00
GO
--測試
--如果Cusno的數據是固定的
Select
ID,
SUM(Case Cusno When 'A ' Then Price Else 0.00 End) As PriceA,
SUM(Case Cusno When 'C ' Then Price Else 0.00 End) As PriceC
From
Table1
Group By
ID
--如果Cusno的數據不是固定的
Declare @S Varchar(8000)
Select @S = 'Select ID '
Select @S = @S + ', SUM(Case Cusno When ' ' ' + Cusno + ' ' ' Then Price Else 0.00 End) As Price '+ Cusno
From Table1 Group By Cusno
Select @S = @S + ' From Table1 Group By ID '
EXEC(@S)
GO
--刪除測試環境
Drop Table Table1
--結果
/*
ID PriceA PriceC
S012 35.02 38.60
S013 52.00 0.00
*/
------解决方案--------------------
向鱼老大学习，我也写个动态的：
declare @v varchar(1000)
set @v= 'select id '