SQL查询结果横向显示到StringGrid
表:sales_Contract
字段内容如下：
goods_ID sales_Contract_Code_Name sales_Number Sales_Money
1 100100XS1 100 200
1 100101XS1 200 400
2 100101XS2 20 100
2 100100XS2 30 150
要求在StringGrid中显示成如下效果:
100100 100101
goods_id sales_Number sales_money sales_number sales_money
1 100 200 200 400
2 30 150 20 100
能否用SQL直接得出这样的结果？
如不能有没速度最快的方法？数据比效多。
[解决办法]
单就你那个表的四条数据是可以实现的 用静态交叉链表 多了也可以实现 没有固定字段的话用动态交叉链表
只不过你可以想下如果有两条完全重复的怎么办
[解决办法]
create table sales_Contract
(
goods_ID int,
sales_Contract_Code_Name varchar(10),
sales_Number int,
Sales_Money int
)
insert into sales_Contract values(1, '100100XS1 ',100,200)
insert into sales_Contract values(1, '100101XS1 ',200,400)
insert into sales_Contract values(2, '100101XS2 ',20 ,100)
insert into sales_Contract values(2, '100100XS2 ',30 ,150)
go
declare @sql varchar(8000)
set @sql = 'select goods_ID '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then sales_Number end) [sales_Number ' + cast(px as varchar) + '] '
+ ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then Sales_Money end) [Sales_Money ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from sales_Contract where goods_ID=a.goods_ID and sales_Number <a.sales_Number)+1 , * from sales_Contract a) t) as a
set @sql = @sql + ' from (select px=(select count(1) from sales_Contract where goods_ID=a.goods_ID and sales_Number <a.sales_Number)+1 , * from sales_Contract a) t group by goods_ID '
exec(@sql)
drop table sales_Contract
/*
goods_ID sales_Number1 Sales_Money1 sales_Number2 Sales_Money2
----------- ------------- ------------ ------------- ------------
1 100 200 200 400
2 20 100 30 150
*/
