22,210
社区成员
发帖
与我相关
我的任务
分享Sql server 查询数据难题！求指点。
数据库中有如下表：
tb1
字段
col1 col2 col3 col4
张三 100 50 20
李四 200 100 0
想查询出类似下面一面的结果
col1 col2
张三 李四
100 200
50 100
20 0
像这样的结果 应该不是简单的行列转换那么简单，目前给出的只有两行数据 查询出来就只有两列，如果在加一个 王五，那么查询处理啊就应该有三列数据。。
请SQL 大人们帮忙看看 应该怎么做！ 在线等
tb1
字段
col1 col2 col3 col4
张三 100 50 20
李四 200 100 0
想查询出类似下面一面的结果
col1 col2
张三 李四
100 200
50 100
20 0
像这样的结果 应该不是简单的行列转换那么简单，目前给出的只有两行数据 查询出来就只有两列，如果在加一个 王五，那么查询处理啊就应该有三列数据。。
请SQL 大人们帮忙看看 应该怎么做！ 在线等
...全文
请发表友善的回复…
发表回复
快溜 2011-08-17
- 打赏
- 举报
在程序里处理吧。
小童 2011-08-17
- 打赏
- 举报
不是任何逻辑都适合在SQL里进行处理的，建议在程序里处理
IE11下面经常卡到爆 2011-08-17
- 打赏
- 举报
SQL 2005以上可以考虑用UNPIVOT函数来实现
或者用存储过程，顶小三
或者用存储过程，顶小三
dalmeeme 2011-08-17
- 打赏
- 举报
我这个不管你是几列几行，都可以的。
dalmeeme 2011-08-17
- 打赏
- 举报
OleDbData md = new OleDbData("select count(*) from 测试");
int columnCount = (int)md.ExecuteScalar();
DataTable dt = new DataTable();
for (int i = 0; i < columnCount; i++)
dt.Columns.Add("列" + i.ToString());
md.ExecuteReader("select * from 测试");
for (int i = 0; i < md.DR.FieldCount; i++)
dt.Rows.Add();
int rowIndex = 0, columnIndex = 0;
while (md.DR.Read())
{
for (rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
dt.Rows[rowIndex][columnIndex] = md.DR[rowIndex].ToString();
columnIndex++;
}
md.Close();
GridView1.DataSource = dt;
GridView1.DataBind();dalmeeme 2011-08-17
- 打赏
- 举报
不用sql的话，可以用ado.net。先用数据阅读器依次读出源表，每读一行，就根据情况填充目标DataTable。关键是确定填充的行号和列表，还有创建的行数和列数。
lester19872007 2011-08-17
- 打赏
- 举报
孟子E章！！！ 你这个在前台代码实现的方案 能达到我想要的效果吗？ 我当时也想过 直接从数据库查询出来数据，然后在前台做处理！ 但是不知道数据库有没有更好的实现方案！！
孟子E章 2011-08-17
- 打赏
- 举报
简单方法
http://dotnet.aspx.cc/file/Convert-GridView-Row-With-Column.aspx
http://dotnet.aspx.cc/file/Convert-GridView-Row-With-Column.aspx
NBDBA 2011-08-17
- 打赏
- 举报
这种变换没有实际意义
如果1000条记录，变成1000个列？那更多呢
无意义的当然懒得写
如果1000条记录，变成1000个列？那更多呢
无意义的当然懒得写
AcHerat 2011-08-17
- 打赏
- 举报
USE [master]
GO
/****** 对象: Table [dbo].[test] 脚本日期: 03/01/2010 16:26:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[id] [int] NULL,
[name] [varchar](20) NULL,
[quarter] [varchar](20) NULL,
[profile] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
.模拟插入数据
id name quarter profile
----------- -------------------- -------------------- --------------------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 行受影响)
执行以下SQL
SELECT * FROM (select * from test
)AS T
pivot (max(T.profile) for T.quarter in([1],[2],[3],[4])) as c
得出结果：
id name 1 2 3 4
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 行受影响)
这样的话，我们就可以成功的翻转过来了，当然反之则就用unpivot
在这又不得不说了，我们的小柯同志对我的这种办法有很大的意见，但始终不为一种解决问题的办法。但只能适用在SQLSERVER中，下面贴出另一种纯T-SQL的标准解决方案（这得辛苦咱们小柯同志。哈哈）
USE [UDTTest]
GO
/****** 对象: StoredProcedure [dbo].[spSelectUDTData] 脚本日期: 03/01/2010 16:38:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* 测试exec spSelectUDTData '表名','列列表','过滤条件','分组','排序','页码','分页大小'
exec spSelectUDTData 'User','UserID,UserName,NickName,Age',null,null,'UserID',5000,10
exec spSelectUDTData 'User',null,'UserID=11005',null,null,null,null
exec spSelectUDTData 'User','Sum(Age) as TotalAge',null,'',null,null,null
*/
ALTER PROCEDURE [dbo].[spSelectUDTData]
@TableName nvarchar(50),
@SelectList nvarchar(4000),
@Condition nvarchar(4000),
@GroupBy nvarchar(4000),
@OrderBy nvarchar(4000),
@PageIndex int,
@PageSize int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
if(@SelectList is null) set @SelectList='*'
if(@Condition is null) set @Condition = ''
if(@GroupBy is null) set @GroupBy = ''
if(@OrderBy is null) set @OrderBy = ''
if(@PageIndex is null) set @PageIndex = 0
if(@PageSize is null) set @PageSize = 0
declare @TotalCount int
declare @ErrorNumber int
declare @TempSql nvarchar(max)
declare @sql nvarchar(max)
declare @ColumnName nvarchar(50)
declare @ColumnCaption nvarchar(50)
declare @StoreColumnName nvarchar(50)
declare @ColumnOrder int
declare @Length int
set @ErrorNumber=100002
--如果没有相关的表结构信息，则不执行查询
select @TotalCount = count(1) from dbo.UDTStoreStructure where TableName=@TableName
if(@TotalCount = 0)
return
--获取所有字段
set @sql = 'select ' + @SelectList +' from (select ' --a.RowID,
select @sql=@sql+ ColumnName+',' from dbo.UDTStoreStructure where TableName=@TableName
set @Length=len(@sql)
set @sql=substring(@sql,1,@Length-1) --删除最后的逗号
-- set @ColumnOrder = 0
-- while(@ColumnOrder < @TotalCount)
-- begin
-- select @ColumnName=ColumnName,@ColumnCaption=ColumnCaption from dbo.UDTStoreStructure where TableName=@TableName and ColumnOrder=@ColumnOrder
--
-- if( @ColumnOrder = @TotalCount - 1)
-- set @sql = @sql + @ColumnName -- +' as '+@ColumnCaption
-- else
-- set @sql = @sql + @ColumnName + ','
--
-- set @ColumnOrder = @ColumnOrder + 1
-- end
--UDTData表中字段的获取
set @sql = @sql + ' from ( select TableName,RowID,'
select @sql=@sql+'t.'+StoreColumnName+ ' as ' + ColumnName+',' from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTData'
set @Length=len(@sql)
set @sql=substring(@sql,1,@Length-1) --删除最后的逗号
-- set @ColumnOrder = 0
-- select @TotalCount = count(1) from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTData'
-- while(@ColumnOrder < @TotalCount)
-- begin
-- if(exists(select 1 from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTData' and ColumnOrder=@ColumnOrder))
-- begin
-- select @StoreColumnName=StoreColumnName,@ColumnName=ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTData' and ColumnOrder=@ColumnOrder
--
-- if( @ColumnOrder = @TotalCount -1)
-- set @sql = @sql + 't.'+ @StoreColumnName + ' as '+ @ColumnName
-- else
-- set @sql = @sql + 't.'+ @StoreColumnName + ' as ' + @ColumnName + ','
-- end
--
-- set @ColumnOrder = @ColumnOrder + 1
-- end
set @sql = @sql + ' from UDTData as t where TableName='''+convert(nvarchar(36), @TableName) +''') a'
--UDTDataDateTime表中字段的获取
if(exists(select top 1 ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataDateTime'))
begin
set @sql = @sql + ' left join '
set @sql = @sql + '(select TableName,RowID'
select @sql = @sql + ', case ColumnName when ''' + ColumnName + ''' then ColumnValue else '''' end ' + ColumnName
from (select ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataDateTime') as b
set @sql = @sql + ' from UDTDataDateTime where TableName='''+convert(nvarchar(36), @TableName) +''' ) b'
+ ' on a.TableName=b.TableName and a.RowID=b.RowID'
end
--UDTDataFloat表中字段的获取
if(exists(select top 1 ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataFloat'))
begin
set @sql = @sql + ' left join '
set @sql = @sql + '(select TableName,RowID'
select @sql = @sql + ', case ColumnName when ''' + ColumnName + ''' then ColumnValue else '''' end ' + ColumnName
from (select ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataFloat') as b
set @sql = @sql + ' from UDTDataFloat where TableName='''+convert(nvarchar(36), @TableName) +''' ) c'
+ ' on a.TableName=c.TableName and a.RowID=c.RowID'
end
--UDTDataLong表中字段的获取
if(exists(select top 1 ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataLong'))
begin
set @sql = @sql + ' left join '
set @sql = @sql + '(select TableName,RowID'
select @sql = @sql + ', case ColumnName when ''' + ColumnName + ''' then ColumnValue else '''' end ' + ColumnName
from (select ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataLong') as b
set @sql = @sql + ' from UDTDataLong where TableName='''+convert(nvarchar(36), @TableName) +''' ) d'
+ ' on a.TableName=d.TableName and a.RowID=d.RowID'
end
--UDTDataString表中字段的获取
if(exists(select top 1 ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataString'))
begin
set @sql = @sql + ' left join '
set @sql = @sql + '(select TableName,RowID'
select @sql = @sql + ', max(case ColumnName when ''' + ColumnName + ''' then ColumnValue else '''' end) ' + ColumnName
from (select ColumnName from dbo.UDTStoreStructure where TableName=@TableName and StoreTableName='UDTDataString') as b
set @sql = @sql + ' from UDTDataString where TableName='''+convert(nvarchar(36), @TableName) +''' group by TableName, RowID) e'
+ ' on a.TableName=e.TableName and a.RowID=e.RowID'
end
set @sql=@sql+') t'
if(len(@Condition) > 0)
set @sql = @sql + ' where ' + @Condition
if(len(@GroupBy) > 0)
set @sql = @sql + ' group by ' + @GroupBy
if(len(@OrderBy) > 0)
set @sql = @sql + ' order by ' + @OrderBy
if @PageIndex > 0 and @PageSize > 0
begin
set @TempSql='select '+@SelectList+' from(select ' + @SelectList + ', ROW_NUMBER() OVER (order by '
if(len(@OrderBy) > 0)
begin
set @TempSql=@TempSql+ @OrderBy + ') as RowNumber'
set @sql=replace(@sql,' order by ' + @OrderBy,'')
end
else
RAISERROR(@ErrorNumber,15,1)
set @TempSql = @TempSql + ' from ( '+ @sql +') tx) tp where RowNumber>'+convert(varchar(10),(@PageIndex-1)*@PageSize) + ' and RowNumber<='+convert(varchar(10),@PageIndex*@PageSize)
set @sql = @TempSql
end
--print @sql
exec(@sql)
END
chuanzhang5687 2011-08-17
- 打赏
- 举报
那就动态行列转换呗
--小F-- 2011-08-17
- 打赏
- 举报
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb--小F-- 2011-08-17
- 打赏
- 举报
动态列转行...
ASPNETCHENGXU 2011-08-17
- 打赏
- 举报
[Quote=引用 8 楼 dalmeeme 的回复:]
我这个不管你是几列几行，都可以的。
[/Quote]
[Quote=引用 8 楼 dalmeeme 的回复:]
我这个不管你是几列几行，都可以的。
[/Quote]
.net的都跑sql来抢分了，呵呵。。
我这个不管你是几列几行，都可以的。
[/Quote]
[Quote=引用 8 楼 dalmeeme 的回复:]
我这个不管你是几列几行，都可以的。
[/Quote]
.net的都跑sql来抢分了，呵呵。。
原来不是怕 2011-08-17
- 打赏
- 举报
建议读取数据后到项目代码实现，写SQL太影响效率了，新手路过
子夜__ 2011-08-17
- 打赏
- 举报
不好意思发错版块了。
子夜__ 2011-08-17
- 打赏
- 举报
[Quote=引用 5 楼 lester19872007 的回复:]
孟子E章！！！ 你这个在前台代码实现的方案 能达到我想要的效果吗？ 我当时也想过 直接从数据库查询出来数据，然后在前台做处理！ 但是不知道数据库有没有更好的实现方案！！
[/Quote]
用前台来实现行列转换
如果有4条记录 那么就有4列了。
孟子E章！！！ 你这个在前台代码实现的方案 能达到我想要的效果吗？ 我当时也想过 直接从数据库查询出来数据，然后在前台做处理！ 但是不知道数据库有没有更好的实现方案！！
[/Quote]
用前台来实现行列转换
如果有4条记录 那么就有4列了。
chuanzhang5687 2011-08-17
- 打赏
- 举报
研究了好大一会。没搞出来
