求一段行列轉換SQL

Question

本帖最后由 decher 于 2011-07-18 14:54:24 编辑

Answer 1

最好能update到同原表中，或者直接select結果出來也可以，報表會用到。

Answer 2

create table #temp 
(
    seq int ,
    brhcode varchar(20),
    order_ref varchar(20),
    o1 varchar(20),
    o2 varchar(20),
    o3 varchar(20),
    o4 varchar(20),
    o5 varchar(20),
    o6 varchar(20),
    o7 varchar(20),
    o8 varchar(20)
)
insert into #temp values
(1,'6101','6101','','','','','','','','')
insert into #temp values
(2,'6101','6102','','','','','','','','')
insert into #temp values
(3,'6101','6103','','','','','','','','')
insert into #temp values
(1,'3991','3901','','','','','','','','')
insert into #temp values
(2,'3991','3902','','','','','','','','')
insert into #temp values
(3,'3991','3903','','','','','','','','')
insert into #temp values
(4,'3991','3904','','','','','','','','')
update a set 
 o1=(select order_ref from #temp where brhcode=a.brhcode and seq=1),
 o2=(select order_ref from #temp where brhcode=a.brhcode and seq=2),
 o3=(select order_ref from #temp where brhcode=a.brhcode and seq=3),
 o4=(select order_ref from #temp where brhcode=a.brhcode and seq=4),
 o5=(select order_ref from #temp where brhcode=a.brhcode and seq=5),
 o6=(select order_ref from #temp where brhcode=a.brhcode and seq=6),
 o7=(select order_ref from #temp where brhcode=a.brhcode and seq=7),
 o8=(select order_ref from #temp where brhcode=a.brhcode and seq=8)
from #temp aselect * from #temp/*
seq         brhcode              order_ref            o1                   o2                   o3                   o4                   o5                   o6                   o7                   o8
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1           6101                 6101                 6101                 6102                 6103                 NULL                 NULL                 NULL                 NULL                 NULL
2           6101                 6102                 6101                 6102                 6103                 NULL                 NULL                 NULL                 NULL                 NULL
3           6101                 6103                 6101                 6102                 6103                 NULL                 NULL                 NULL                 NULL                 NULL
1           3991                 3901                 3901                 3902                 3903                 3904                 NULL                 NULL                 NULL                 NULL
2           3991                 3902                 3901                 3902                 3903                 3904                 NULL                 NULL                 NULL                 NULL
3           3991                 3903                 3901                 3902                 3903                 3904                 NULL                 NULL                 NULL                 NULL
4           3991                 3904                 3901                 3902                 3903                 3904                 NULL                 NULL                 NULL                 NULL(7 行受影响)

Answer 3

--測試數據
create table #temp 
(
    seq int ,
    brhcode varchar(20),
    order_ref varchar(20),
    o1 varchar(20),
    o2 varchar(20),
    o3 varchar(20),
    o4 varchar(20),
    o5 varchar(20),
    o6 varchar(20),
    o7 varchar(20),
    o8 varchar(20)
)
insert into #temp values
(1,'6101','6101','','','','','','','','')
insert into #temp values
(2,'6101','6102','','','','','','','','')
insert into #temp values
(3,'6101','6103','','','','','','','','')
insert into #temp values
(1,'3991','3901','','','','','','','','')
insert into #temp values
(2,'3991','3902','','','','','','','','')
insert into #temp values
(3,'3991','3903','','','','','','','','')
insert into #temp values
(4,'3991','3904','','','','','','','','')

Answer 4

*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------不会的话再帮你写

Answer 5

CREATE TABLE t(序列 INT, 分店 VARCHAR(10), 訂單編號 VARCHAR(10))
INSERT dbo.t
SELECT 1, '分店1', '1000001' UNION ALL 
SELECT 2, '分店1', '1000002' UNION ALL  
SELECT 3, '分店1', '1000003' UNION ALL  
SELECT 1, '分店2', '2000001' UNION ALL  
SELECT 2, '分店2', '2000002' UNION ALL  
SELECT 3, '分店2', '2000003' UNION ALL  
SELECT 4, '分店2', '2000004';DECLARE @sql VARCHAR(8000);
SELECT @sql='select 分店,';
SELECT @sql=@sql+'isnull(max(case when RIGHT(訂單編號,1)='''+訂單編號+''' then 訂單編號 end),'''') as o'+訂單編號+','
from (SELECT DISTINCT RIGHT(訂單編號,1) AS 訂單編號 from dbo.t)a
SELECT @sql=SUBSTRING(@sql,0,LEN(@sql))+' from t group by 分店'
--PRINT @sql
EXEC (@sql);
DROP TABLE dbo.t
/*
分店         o1         o2         o3         o4
---------- ---------- ---------- ---------- ----------
分店1        1000001    1000002    1000003    
分店2        2000001    2000002    2000003    2000004
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)
*/

Answer 6

巧妙利用RIGHT(訂單編號,1) AS 訂單編號 ，不错，再调整一下
CREATE TABLE t(序列 INT, 分店 VARCHAR(10), 訂單編號 VARCHAR(10))
INSERT dbo.t
SELECT 1, '分店1', '1000001' UNION ALL 
SELECT 2, '分店1', '1000002' UNION ALL  
SELECT 3, '分店1', '1000003' UNION ALL  
SELECT 1, '分店2', '2000001' UNION ALL  
SELECT 2, '分店2', '2000002' UNION ALL  
SELECT 3, '分店2', '2000003' UNION ALL  
SELECT 4, '分店2', '2000004';DECLARE @sql VARCHAR(8000);
SELECT @sql='select 分店';
SELECT @sql=@sql+',isnull(max(case when right(訂單編號,6)='''
           +right(訂單編號,6)+''' then 訂單編號 end),'''') as o'+right(訂單編號,1)
from  dbo.tSELECT @sql=@sql+' from t group by 分店'
EXEC (@sql)
/*
分店         o1         o2         o3         o1         o2         o3         o4         
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
分店1        1000001    1000002    1000003    1000001    1000002    1000003    
分店2        2000001    2000002    2000003    2000001    2000002    2000003    2000004（所影响的行数为 2 行）
*/

Answer 7

DECLARE @sql VARCHAR(8000);
SELECT @sql='select 分店 ';
SELECT @sql=@sql+',isnull(max(case when RIGHT(订单编号,1)='''
           +订单编号+''' then 订单编号 end),0) as o'+订单编号
from  (select distinct right(订单编号,1)  as 订单编号 from t) b
SELECT @sql=@sql+' from t group by 分店'
EXEC (@sql)

Answer 8

if object_id('[t]') is not null drop table [t]
go 
CREATE TABLE t(序列 INT, 分店 VARCHAR(10), 订单编号 VARCHAR(10))
INSERT dbo.t
SELECT 1, '分店1', '1000001' UNION ALL 
SELECT 2, '分店1', '1000002' UNION ALL  
SELECT 3, '分店1', '1000003' UNION ALL  
SELECT 1, '分店2', '2000001' UNION ALL  
SELECT 2, '分店2', '2000002' UNION ALL  
SELECT 3, '分店2', '2000003' UNION ALL  
SELECT 4, '分店2', '2000004';
/*
轉換后如：
分店 o1 o2 o3 o4
分店1 1000001 1000002 1000003  
分店2 2000001 2000002 2000003 2000004  */DECLARE @sql VARCHAR(8000);
SELECT @sql='select 分店 ';
SELECT @sql=@sql+',isnull(max(case when RIGHT(订单编号,1)='''
           +订单编号+''' then 订单编号 end),0) as o'+订单编号
from  (select distinct right(订单编号,1)  as 订单编号 from t) b
SELECT @sql=@sql+' from t group by 分店'
EXEC (@sql)

Answer 9

遇到这种情况 我都是把数据放到excel里,然后一个按钮 行列互转,
然后在导入数据库...........
个人认为,手头上工具并不一定就局限到一种

Answer 10

create table #temp 
(
  序列 int,
  分店 nvarchar(50),
  訂單編號 nvarchar(50)
)
insert #temp
select 1,'分店1','1000001' union all
select 2,'分店1','1000002' union all
select 3,'分店1','1000003' union all 
select 1,'分店2','2000001' union all  
select 2,'分店2','2000002' union all  
select 3,'分店2','2000003' union all  
select 4,'分店2','2000004' declare @sql as nvarchar(4000)
set @sql='select 分店'
select @sql=@sql+',max(case when 序列='+ltrim(序列)+' then 訂單編號 else '''' end) as ''o'+ltrim(序列)+''''
from (select distinct 序列 from #temp) as T
set @sql=@sql+' from #temp group by 分店'
exec(@sql)--分店      o1      o2      o3      o4
---------   ------- ------- ------- ---------
--分店1     1000001 1000002 1000003 
--分店2     2000001 2000002 2000003 2000004

Answer 11

select 分店,[o1],[o2],[o3],[o4] from pivot(avg(訂單編號) for 分店 in(o1,o2,o3,o4)) as t

Answer 12

select * from(
select 分店,订单编号,
ROW_NUMBER() OVER(PARTITION BY 分店 ORDER BY  订单编号) as 分店号
from #t) as Src
PIVOT(MAX(订单编号) FOR 分店号 IN ([1],[2],[3],[4]) )as PVt

Answer 13

SQLServer中行转列及列转行且加平均值及汇总值的Sql2000与Sql2005示例 
http://www.lmwlove.com/ac/ID451

Answer 14

pivot，没使用过，学习大家的办法，行列转换确实是常用到的

Answer 15

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

Answer 16

CREATE TABLE t(序列 INT, 分店 VARCHAR(10), 訂單編號 VARCHAR(10))
go
INSERT dbo.t
SELECT 1, '分店1', '1000001' UNION ALL 
SELECT 2, '分店1', '1000002' UNION ALL  
SELECT 3, '分店1', '1000003' UNION ALL  
SELECT 1, '分店2', '2000001' UNION ALL  
SELECT 2, '分店2', '2000002' UNION ALL  
SELECT 3, '分店2', '2000003' UNION ALL  
SELECT 4, '分店2', '2000004';
SELECT * FROM t
go 
 select 分店 ,
MAX(case when RIGHT(訂單編號,1)='1' then 訂單編號 else '' end) as o1,
MAX(case when RIGHT(訂單編號,1)='2' then 訂單編號 else '' end) as o2,
MAX(case when RIGHT(訂單編號,1)='3' then 訂單編號 else '' end) as o3,
MAX(case when RIGHT(訂單編號,1)='4' then 訂單編號 else '' end) as o4
from t 
group by 分店