Sql server 查询数据难题！求指点。

Question

数据库中有如下表：
tb1
字段
col1    col2     col3   col4张三     100      50     20 李四     200      100    0
想查询出类似下面一面的结果col1   col2    张三   李四100    20050     10020     0像这样的结果 应该不是简单的行列转换那么简单，目前给出的只有两行数据 查询出来就只有两列，如果在加一个 王五，那么查询处理啊就应该有三列数据。。请SQL 大人们帮忙看看 应该怎么做！ 在线等

Answer 1

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     41    a      1000    2000  4000  50002    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

Answer 2

简单方法
http://dotnet.aspx.cc/file/Convert-GridView-Row-With-Column.aspx

Answer 3

不用sql的话，可以用ado.net。先用数据阅读器依次读出源表，每读一行，就根据情况填充目标DataTable。关键是确定填充的行号和列表，还有创建的行数和列数。

Answer 4

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();

Answer 5

SQL 2005以上可以考虑用UNPIVOT函数来实现
或者用存储过程，顶小三

Answer 6

不是任何逻辑都适合在SQL里进行处理的，建议在程序里处理

Answer 7

建议读取数据后到项目代码实现，写SQL太影响效率了，新手路过

Answer 8

.net的都跑sql来抢分了，呵呵。。

Answer 9

/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 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 enddrop table tb