select orderInfo.onum,sizeType.sname from orderInfo inner join sizeType on orderInfo.oid=sizeType.oid
查询结果是:onum sname
-----------------
80 M
90 L
95 XL
我想要的结果是: M L XL
------------------
80 90 95
就是将查询出来的sname那一列作为表头,onum作为值显示,有哪位高手指教指教啊
查询结果是:onum sname
-----------------
80 M
90 L
95 XL
我想要的结果是: M L XL
------------------
80 90 95
就是将查询出来的sname那一列作为表头,onum作为值显示,有哪位高手指教指教啊
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238
select sum(onum) as M,
sum(onum) as L,
sum(onum) as XL
from
(
select orderInfo.onum,sizeType.sname from orderInfo inner join sizeType on orderInfo.oid=sizeType.oid
) a
group by sname
@MachineID int
as
Begin
Declare @Sql nvarchar(4000)
Set @Sql = 'Select Distinct MachineID,MachineName' Select @Sql = @Sql + ',Sum(case When MachineName = '''+ MachinePara_CN + ''' Then MachinePara_CN Else 0 End ) as '''+MachinePara_CN+''''
From MachineParamsView Where MachineID = @MachineID Set @Sql = @Sql + ' From MachineParamsView Where MachineID = ' + str(@MachineID) + ' Group By MachineID,MachineName,MachinePara_CN'
Print @Sql
execute sp_executesql @Sql
End
ALTER Proc [dbo].[MachineParamsDetail]
@oid int
as
Begin
Declare @Sql nvarchar(4000)
Set @Sql = 'Select Distinct sizeType.oid,sizeType.sname ' Select @Sql = @Sql + ',Sum(case When sizeType.oid= '''+ oid+ ''' Then orderInfo.onum Else 0 End ) as '''+Values+''''
From sizeType Where oid= @oid Set @Sql = @Sql + ' From orderInfo inner join sizeType on orderInfo.oid=sizeType.oid Group By sizeType.oid,sizeType.sname
Print @Sql
execute sp_executesql @Sql
End
INSERT INTO @OrderInfo(onum,sname)
SELECT 80,'M' UNION ALL
SELECT 90,'L' UNION ALL
SELECT 95,'XL'
SELECT * FROM @OrderInfo
PIVOT(MAX(onum) FOR sname IN ([M],[L],[XL])) AS T写死了列还好,动态的话麻烦了...
--------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[orderInfo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[onum] [varchar](50) NOT NULL,
[sname] [varchar](50) NOT NULL,
[add_time] [datetime] NULL CONSTRAINT [DF_orderInfo_add_time] DEFAULT (getdate()),
CONSTRAINT [PK_orderInfo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF存储过程
-----------------------------------------------------------
create PROCEDURE [dbo].[pc_colums]
AS
BEGIN
create table #temp(ID int)
insert into #temp select ID from orderInfo
declare @strSql varchar(500)
declare @sqlfiled varchar(1000),@selSql varchar(1000)
declare @onum varchar(10),@sname varchar(50),@id int
set @sqlfiled='alter table #temp add '
set @selSql='select distinct '
declare createname cursor for select onum from orderInfo
open createname
fetch next from createname into @onum
while(@@fetch_status=0)
begin
set @sqlfiled=@sqlfiled+@onum+' varchar(10),'
set @selSql=@selSql+@onum+','
fetch next from createname into @onum
end
close createname
deallocate createname
set @sqlfiled=substring(@sqlfiled,1,len(@sqlfiled)-1)
exec (@sqlfiled)
print @sqlfiled
declare curname cursor for select onum,sname from orderInfo
open curname
fetch next from curname into @onum,@sname
while(@@fetch_status=0)
begin
declare curId cursor for select ID from #temp
open curId
fetch next from curId into @id
while(@@fetch_status=0)
begin
set @strSql=''
set @strSql='update #temp set '+@onum+'='+@sname+' where ID=convert(int,'+cast(@id as varchar)+')'
exec(@strSql)
fetch next from curId into @id
end
close curId
deallocate curId
fetch next from curname into @onum,@sname
end
close curname
deallocate curname set @selSql=substring(@selSql,1,len(@selSql)-1)+' from #temp'
print @selSql
exec(@selSql)
drop table #temp
END
一个英文的,一个翻译的:
http://apicode.gicp.net/class.do?api=selectByfatherIndex&father=255
http://apicodecn.gicp.net/class.do?api=selectByfatherIndex&father=255