declare @sql varchar(8000)
set @sql='select formid '
select @sql=@sql+',max(case foodid when '+ ltrim(foodid) +'
then foodnumber else 0 end) as ['+ltrim(foodname)+']'
from (select distinct foodname,foodid from tb_GA_OrderMeal as a
left outer join tb_GA_MealType as b on a.foodid = b.id)as c
set @sql=@sql+' from tb_GA_OrderMeal group by formid'
exec(@sql)
我现在用语句能查询到要的结果，但是我怎么能把这个结果变成一个视图？ 这是个动态横变竖 查询。
------解决方案--------------------
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-10 13:40:23
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[tb_GA_OrderMeal]
if object_id('[tb_GA_OrderMeal]') is not null drop table [tb_GA_OrderMeal]
go
create table [tb_GA_OrderMeal]([foodid] int,[foodnumber] int,[formid] int)
insert [tb_GA_OrderMeal]
select 1,20,1 union all
select 2,24,1 union all
select 3,22,1 union all
select 1,30,2 union all
select 2,31,2
--> 测试数据：[tb_GA_MealType]
if object_id('[tb_GA_MealType]') is not null drop table [tb_GA_MealType]
go
create table [tb_GA_MealType]([id] int,[foodname] varchar(4))
insert [tb_GA_MealType]
select 1,'早饭' union all
select 2,'午饭' union all
select 3,'晚饭'
--------------开始查询--------------------------
CREATE PROC test
AS
declare @sql varchar(8000)
set @sql='select formid '
select @sql=@sql+',max(case foodid when '+ ltrim(foodid) +'
then foodnumber else 0 end) as ['+ltrim(foodname)+']'
from (select distinct foodname,foodid from tb_GA_OrderMeal as a
left outer join tb_GA_MealType as b on a.foodid = b.id)as c
set @sql=@sql+' from tb_GA_OrderMeal group by formid'
exec(@sql)
EXEC test
----------------结果----------------------------
/*
formid 晚饭 午饭 早饭
----------- ----------- ----------- -----------
1 22 24 20
2 0 31 30
*/
------解决方案--------------------
create table tb_GA_OrderMeal
(foodid int,foodnumber int,formid int)
insert into tb_GA_OrderMeal
select 1, 20, 1 union all
select 2, 24, 1 union all
select 3, 22, 1 union all
select 1, 30, 2 union all
select 2, 31, 2
create table tb_GA_MealType
(id int,foodname varchar(10))
insert into tb_GA_MealType
select 1,'早饭' union all
select 2,'午饭' union all
select 3,'晚饭'
select formid,
isnull([早饭],0) '早饭',
isnull([午饭],0) '午饭',
isnull([晚饭],0) '晚饭'
from
(select a.formid,b.foodname,a.foodnumber
from tb_GA_OrderMeal a
inner join tb_GA_MealType b on a.foodid=b.id) a
pivot(max(foodnumber) for foodname in([早饭],[午饭],[晚饭])) p
/*
formid 早饭 午饭 晚饭
----------- ----------- ----------- -----------