select Name, sum(case when [Title]=Asp.net开发基础 then quantity else 0 end) as [Asp.net开发基础], sum(case when [Title]=Java 2005从入门到精通 then quantity else 0 end) as [Java 2005从入门到精通], sum(case when [Title]=Visual C# 2005从入门到精通 then quantity else 0 end) as [Visual C# 2005从入门到精通] from BookshopPlus.dbo.BuyList group by Name --字符串值没加引号 +'sum(case when [Title]='''+ltrim([Title])+''' then quantity else 0 end) as ['+
看的辛苦,直接帮你写一个算了---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-09-18 13:57:43 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:dbo.[BuyList] if object_id('dbo.[BuyList]') is not null drop table dbo.[BuyList] go create table dbo.[BuyList]([Name] varchar(4),[Title] varchar(24),[Quantity] int) insert dbo.[BuyList] select '张三','VisualC#2005从入门到精通',1 union all select '李四','Java2005从入门到精通',2 union all select '李四','VisualC#2005从入门到精通',3 union all select '王五','Asp.net开发基础',1 --------------开始查询-------------------------- declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([Title])+'=max(case when [Title]='+quotename([Title],'''')+' then [Quantity] else 0 end)' from [BuyList] group by [Title] exec('select [Name]'+@s+' from [BuyList] group by [Name]') ----------------结果---------------------------- /* Name Asp.net开发基础 Java2005从入门到精通 VisualC#2005从入门到精通 ---- ----------- -------------- ------------------ 李四 0 2 3 王五 1 0 0 张三 0 0 1 */
因为你table的列类型有中文,所以为了能够正常存储,你的sql定义应该是nvarchar(8000) declare @sql nvarchar(MAX)declare @sql1 nvarchar(MAX) select @sql=isnull(@sql+',','') +'sum(case when [Title]=N'''+ltrim([Title])+''' then quantity else 0 end) as ['+ltrim([Title])+']' from (select distinct [Title] from dbo.BuyList) t
--print @sql SELECT @sql1='select Name,'+@sql+' from dbo.BuyList group by Name' --print @sql1 exec sp_executesql @sql1 以上的脚本我执行是执行通的
我的表定义是这样的,应该跟你的类似: CREATE TABLE dbo.BuyList ( id INT IDENTITY(1,1) NOT NULL, Name nvarchar(10) null, Title nvarchar(50) null, Quantity int null ) GOINSERT INTO dbo.BuyList SELECT N'张三',N'Visual C# 2005从入门到精通',1 UNION ALL SELECT N'李四',N'Java 2005从入门到精通',2 UNION ALL SELECT N'李四',N'Visual C# 2005从入门到精通',3 UNION ALL SELECT N'王五',N'Asp.net开发基础',1 GO
select name,isnull([Visual C# 2005从入门到精通],0)[Visual C# 2005从入门到精通], isnull([Java 2005从入门到精通],0)[Java 2005从入门到精通],ISNULL([Asp.net开发基础],0)[Asp.net开发基础] from t pivot (max(quantity)for title in ([Visual C# 2005从入门到精通],[Java 2005从入门到精通],[Asp.net开发基础]))t1 order by name desc
在数据查询后写个循环动态创建列。没gridview,给你个例子 for I:=0 to cdsMaster.Fields.Count-1 do begin cxGrid3DBTableView1.CreateColumn; cxGrid3DBTableView1.Columns[I].DataBinding.FieldName := cdsMaster.Fields[I].DisplayName; cxGrid3DBTableView1.Columns[I].Caption := cdsMaster.Fields[I].DisplayName; end;
sum(case when [Title]=Asp.net开发基础 then quantity else 0 end) as [Asp.net开发基础],
sum(case when [Title]=Java 2005从入门到精通 then quantity else 0 end) as [Java 2005从入门到精通],
sum(case when [Title]=Visual C# 2005从入门到精通 then quantity else 0 end) as [Visual C# 2005从入门到精通]
from BookshopPlus.dbo.BuyList
group by Name
--字符串值没加引号
+'sum(case when [Title]='''+ltrim([Title])+''' then quantity else 0 end) as ['+
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-18 13:57:43
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:dbo.[BuyList]
if object_id('dbo.[BuyList]') is not null drop table dbo.[BuyList]
go
create table dbo.[BuyList]([Name] varchar(4),[Title] varchar(24),[Quantity] int)
insert dbo.[BuyList]
select '张三','VisualC#2005从入门到精通',1 union all
select '李四','Java2005从入门到精通',2 union all
select '李四','VisualC#2005从入门到精通',3 union all
select '王五','Asp.net开发基础',1
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Title])+'=max(case when [Title]='+quotename([Title],'''')+' then [Quantity] else 0 end)'
from [BuyList] group by [Title]
exec('select [Name]'+@s+' from [BuyList] group by [Name]')
----------------结果----------------------------
/*
Name Asp.net开发基础 Java2005从入门到精通 VisualC#2005从入门到精通
---- ----------- -------------- ------------------
李四 0 2 3
王五 1 0 0
张三 0 0 1
*/
declare @sql nvarchar(MAX)declare @sql1 nvarchar(MAX)
select
@sql=isnull(@sql+',','')
+'sum(case when [Title]=N'''+ltrim([Title])+''' then quantity else 0 end) as ['+ltrim([Title])+']'
from
(select distinct [Title] from dbo.BuyList) t
--print @sql
SELECT @sql1='select Name,'+@sql+' from dbo.BuyList group by Name'
--print @sql1
exec sp_executesql @sql1
以上的脚本我执行是执行通的
CREATE TABLE dbo.BuyList
(
id INT IDENTITY(1,1) NOT NULL,
Name nvarchar(10) null,
Title nvarchar(50) null,
Quantity int null
)
GOINSERT INTO dbo.BuyList
SELECT N'张三',N'Visual C# 2005从入门到精通',1
UNION ALL
SELECT N'李四',N'Java 2005从入门到精通',2
UNION ALL
SELECT N'李四',N'Visual C# 2005从入门到精通',3
UNION ALL
SELECT N'王五',N'Asp.net开发基础',1
GO
把动态列名加到gridview中。
isnull([Java 2005从入门到精通],0)[Java 2005从入门到精通],ISNULL([Asp.net开发基础],0)[Asp.net开发基础]
from t pivot (max(quantity)for title in ([Visual C# 2005从入门到精通],[Java 2005从入门到精通],[Asp.net开发基础]))t1
order by name desc
cxGrid3DBTableView1.CreateColumn;
cxGrid3DBTableView1.Columns[I].DataBinding.FieldName := cdsMaster.Fields[I].DisplayName;
cxGrid3DBTableView1.Columns[I].Caption := cdsMaster.Fields[I].DisplayName;
end;