22,207
社区成员
发帖
与我相关
我的任务
分享MSSQL列转行的问题
有下面这样的数据
ContainerName FRD_RateName FRD_Charges
---------------------------------------------------------------------------------------------------------
45FR 标准报价 4000
45FR 箱扣100 41
45FR 退佣10 1
45GP 标准报价 4100
45GP 箱扣100 42
45GP 退佣10 2
40GP 标准报价 4200
40GP 箱扣100 43
40GP 退佣10 3
20GP 标准报价 4300
20GP 箱扣100 44
20GP 退佣10 4
如何变成这样的格式：
FRD_RateName 45FR 45GP 40GP 20GP
标准报价 4000 4100 4200 4300
箱扣100 41 42 43 44
退佣10 1 2 3 4
要这样转换这个sql要怎么写啊，而且上面的ContainerName中45FR，45GP是不固定的
有可能是别的有可能是44GP，32GP之类的是动态的，
求高手帮忙啊。。
ContainerName FRD_RateName FRD_Charges
---------------------------------------------------------------------------------------------------------
45FR 标准报价 4000
45FR 箱扣100 41
45FR 退佣10 1
45GP 标准报价 4100
45GP 箱扣100 42
45GP 退佣10 2
40GP 标准报价 4200
40GP 箱扣100 43
40GP 退佣10 3
20GP 标准报价 4300
20GP 箱扣100 44
20GP 退佣10 4
如何变成这样的格式：
FRD_RateName 45FR 45GP 40GP 20GP
标准报价 4000 4100 4200 4300
箱扣100 41 42 43 44
退佣10 1 2 3 4
要这样转换这个sql要怎么写啊，而且上面的ContainerName中45FR，45GP是不固定的
有可能是别的有可能是44GP，32GP之类的是动态的，
求高手帮忙啊。。
...全文
请发表友善的回复…
发表回复
zhaojm0405 2013-08-09
- 打赏
- 举报
Shawn 2013-08-08
- 打赏
- 举报
#1.两个的效率一样。因为实际上PIVOT内部也是转换成CASE WHEN再去执行的。
#2.可以加到里面来。
create table q14
(ContainerID int, FRD_RateName varchar(12), FRD_Charges INT,FRD_RateType int)
insert into q14
select 1, '标准报价', 4000,1 union all
select 1, '箱扣100', 41,1 union all
select 1, '退佣10', 1,2 union all
select 2, '标准报价', 4100,1 union all
select 2, '箱扣100', 42,1 union all
select 2, '退佣10', 2,2 union all
select 3, '标准报价', 4200,1 union all
select 3, '箱扣100', 43,1 union all
select 3, '退佣10', 3,2 union all
select 4, '标准报价', 4300,1 union all
select 4, '箱扣100', 44,1 union all
select 4, '退佣10', 4,2
create table Container
(ContainerID int, ContainerName varchar(12))
insert into Container
select 1, '45GP' union all
select 2, '45FR' union all
select 3, '40GP' union all
select 4, '20GP'
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'max(case when b.ContainerName='''+ContainerName+''' then a.FRD_Charges else 0 end) ['+ContainerName+']'
from (select distinct ContainerName from Container) t
order by t.ContainerName desc
select @tsql='select a.FRD_RateName,'+@tsql+',a.FRD_RateType'
+' from q14 a '
+' inner join Container b on a.ContainerID=b.ContainerID '
+' group by a.FRD_RateName, a.FRD_RateType '
exec(@tsql)
/*
FRD_RateName 45GP 45FR 40GP 20GP FRD_RateType
标准报价 4000 4100 4200 4300 1
退佣10 1 2 3 4 2
箱扣100 41 42 43 44 1
*/
-Tracy-McGrady- 2013-08-08
- 打赏
- 举报
又一个想得到现成答案的
为乐而来 2013-08-08
- 打赏
- 举报
@ap0405140
不知道这一列FRD_RateType是不是也可以加到这里面的呀
为乐而来 2013-08-08
- 打赏
- 举报
@
我想问一下pivot for方法与case when这二种方法哪种效率会高一些
还有一个问题就是其实这个表还有一列FRD_RateType这个列的值是1或2
FRD_RateName 45GP 45FR 40GP 20GP FRD_RateType
------------ ----------- ----------- ---------------------------------------
标准报价 4000 4100 4200 4300 1
退佣10 1 2 3 4 2
箱扣100 41 42 43 44 1
524929657雯 2013-08-08
- 打赏
- 举报
chen357313771 2013-08-08
- 打赏
- 举报
CREATE TABLE #MyTable(
ContainerName VARCHAR(12)
, FRD_RateName NVARCHAR(12)
, FRD_Charges INT
)
INSERT INTO #MyTable
SELECT '45FR',N'标准报价',4000
UNION
SELECT '45FR',N'箱扣100',41
UNION
SELECT '45FR',N'退佣10 ',1
UNION
SELECT '45GP',N'标准报价',4100
UNION
SELECT '45GP',N'箱扣100 ',42
UNION
SELECT '45GP',N'退佣10',2
UNION
SELECT '40GP',N'标准报价',4200
UNION
SELECT '40GP', N'箱扣100', 43
UNION
SELECT '40GP',N'退佣10', 3
UNION
SELECT '20GP', N'标准报价',4300
UNION
SELECT '20GP',N'箱扣100', 44
UNION
SELECT '20GP',N'退佣10',4
DECLARE @Sql VARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+'['+ContainerName+']' FROM #MyTable GROUP BY ContainerName
SELECT @Sql='SELECT *
FROM #MyTable a
PIVOT(MAX(FRD_Charges) FOR ContainerName IN('+@Sql+')) p '
EXEC(@Sql)
DROP TABLE #MyTable
唐诗三百首 2013-08-08
- 打赏
- 举报
方法2,
create table q14
(ContainerID int, FRD_RateName varchar(12), FRD_Charges int)
insert into q14
select 1, '标准报价', 4000 union all
select 1, '箱扣100', 41 union all
select 1, '退佣10', 1 union all
select 2, '标准报价', 4100 union all
select 2, '箱扣100', 42 union all
select 2, '退佣10', 2 union all
select 3, '标准报价', 4200 union all
select 3, '箱扣100', 43 union all
select 3, '退佣10', 3 union all
select 4, '标准报价', 4300 union all
select 4, '箱扣100', 44 union all
select 4, '退佣10', 4
create table Container
(ContainerID int, ContainerName varchar(12))
insert into Container
select 1, '45GP' union all
select 2, '45FR' union all
select 3, '40GP' union all
select 4, '20GP'
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+ContainerName+']'
from (select distinct ContainerName from Container) t
order by t.ContainerName desc
select @tsql='select FRD_RateName,'+@tsql
+' from (select a.FRD_RateName,b.ContainerName,a.FRD_Charges from q14 a '
+' inner join Container b on a.ContainerID=b.ContainerID) c '
+' pivot(max(FRD_Charges) for ContainerName in ('+@tsql+')) p '
exec(@tsql)
/*
FRD_RateName 45GP 45FR 40GP 20GP
------------ ----------- ----------- ----------- -----------
标准报价 4000 4100 4200 4300
退佣10 1 2 3 4
箱扣100 41 42 43 44
(3 row(s) affected)
*/
lzw_0736 2013-08-08
- 打赏
- 举报
CREATE TABLE #cu0 (ContainerID int,ContainerName varCHAR(10))
INSERT INTO #cu0
SELECT 1,'45GP' UNION ALL
SELECT 2,'45FR' UNION ALL
SELECT 3,'40GP' UNION ALL
SELECT 4,'20GP'
CREATE TABLE #cu1 (ContainerName varCHAR(10),FRD_RateName varCHAR(10),FRD_Charges INT)
INSERT INTO #cu1
SELECT '45FR','标准报价',4000 UNION ALL
SELECT '45FR','箱扣100',41 UNION ALL
SELECT '45FR','退佣10',1 UNION ALL
SELECT '45GP','标准报价',4100 UNION ALL
SELECT '45GP','箱扣100',42 UNION ALL
SELECT '45GP','退佣10',2 UNION ALL
SELECT '40GP','标准报价',4200 UNION ALL
SELECT '40GP','箱扣100',43 UNION ALL
SELECT '40GP','退佣10',3 UNION ALL
SELECT '20GP','标准报价',4300 UNION ALL
SELECT '20GP','箱扣100',44 UNION ALL
SELECT '20GP','退佣10',4
declare @sql varchar(max),@sql2 varchar(max)
select @sql = isnull(@sql + '],[' , '') + ContainerName from #cu0 ORDER BY ContainerID
set @sql = '[' + @sql + ']'
select @sql2 = isnull(@sql2 + ',' , ',') + 'isnull(['+ContainerName+'],0) ['+ContainerName+']' from #cu0
set @sql='select FRD_RateName'+@sql2+' from #cu1 a pivot (max(FRD_Charges) for ContainerName in (' + @sql + ')) b
order by case when FRD_RateName=''标准报价'' then 1 when FRD_RateName=''退佣10'' then 2 else 3 end'
exec (@sql)
唐诗三百首 2013-08-08
- 打赏
- 举报
create table q14
(ContainerID int, FRD_RateName varchar(12), FRD_Charges int)
insert into q14
select 1, '标准报价', 4000 union all
select 1, '箱扣100', 41 union all
select 1, '退佣10', 1 union all
select 2, '标准报价', 4100 union all
select 2, '箱扣100', 42 union all
select 2, '退佣10', 2 union all
select 3, '标准报价', 4200 union all
select 3, '箱扣100', 43 union all
select 3, '退佣10', 3 union all
select 4, '标准报价', 4300 union all
select 4, '箱扣100', 44 union all
select 4, '退佣10', 4
create table Container
(ContainerID int, ContainerName varchar(12))
insert into Container
select 1, '45GP' union all
select 2, '45FR' union all
select 3, '40GP' union all
select 4, '20GP'
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'max(case when b.ContainerName='''+ContainerName+''' then a.FRD_Charges else 0 end) ['+ContainerName+']'
from (select distinct ContainerName from Container) t
order by t.ContainerName desc
select @tsql='select a.FRD_RateName,'+@tsql
+' from q14 a '
+' inner join Container b on a.ContainerID=b.ContainerID '
+' group by a.FRD_RateName '
exec(@tsql)
/*
FRD_RateName 45GP 45FR 40GP 20GP
------------ ----------- ----------- ----------- -----------
标准报价 4000 4100 4200 4300
退佣10 1 2 3 4
箱扣100 41 42 43 44
(3 row(s) affected)
*/
lzw_0736 2013-08-08
- 打赏
- 举报
CREATE TABLE #cu1 (ContainerName varCHAR(10),FRD_RateName varCHAR(10),FRD_Charges INT)
INSERT INTO #cu1
SELECT '45FR','标准报价',4000 UNION ALL
SELECT '45FR','箱扣',41 UNION ALL
SELECT '45FR','退佣',1 UNION ALL
SELECT '45GP','标准报价',4100 UNION ALL
SELECT '45GP','箱扣',42 UNION ALL
SELECT '45GP','退佣',2 UNION ALL
SELECT '40GP','标准报价',4200 UNION ALL
SELECT '40GP','箱扣',43 UNION ALL
SELECT '40GP','退佣',3 UNION ALL
SELECT '20GP','标准报价',4300 UNION ALL
SELECT '20GP','箱扣',44 UNION ALL
SELECT '20GP','退佣',4
SELECT DISTINCT ContainerName INTO #cu FROM #cu1
declare @sql varchar(max),@sql2 varchar(max)
select @sql = isnull(@sql + '],[' , '') + ContainerName from #cu
set @sql = '[' + @sql + ']'
select @sql2 = isnull(@sql2 + ',' , ',') + 'isnull(['+ContainerName+'],0) ['+ContainerName+']' from #cu
set @sql='select FRD_RateName'+@sql2+' from #cu1 a pivot (max(FRD_Charges) for ContainerName in (' + @sql + ')) b'
exec (@sql)
为乐而来 2013-08-08
- 打赏
- 举报
其实这个ContainerName本来是一个ID要从别的表关联进来的
ContainerID FRD_RateName FRD_Charges
---------------------------------------------------------------------------------------------------------
1 标准报价 4000
1 箱扣100 41
1 退佣10 1
2 标准报价 4100
2 箱扣100 42
2 退佣10 2
3 标准报价 4200
3 箱扣100 43
3 退佣10 3
4 标准报价 4300
4 箱扣100 44
4 退佣10 4
我怎么
另一个Container表
结构
ContainerID ContainerName
1 45GP
2 45FR
3 40GP
4 20GP
唐诗三百首 2013-08-08
- 打赏
- 举报
方法2,
create table q14
(ContainerName varchar(12), FRD_RateName varchar(12), FRD_Charges int)
insert into q14
select '45FR', '标准报价', 4000 union all
select '45FR', '箱扣100', 41 union all
select '45FR', '退佣10', 1 union all
select '45GP', '标准报价', 4100 union all
select '45GP', '箱扣100', 42 union all
select '45GP', '退佣10', 2 union all
select '40GP', '标准报价', 4200 union all
select '40GP', '箱扣100', 43 union all
select '40GP', '退佣10', 3 union all
select '20GP', '标准报价', 4300 union all
select '20GP', '箱扣100', 44 union all
select '20GP', '退佣10', 4
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+ContainerName+']'
from (select distinct ContainerName from q14) t
order by t.ContainerName desc
select @tsql='select FRD_RateName,'+@tsql
+' from q14 a '
+' pivot(max(FRD_Charges) for ContainerName in('+@tsql+')) p '
exec(@tsql)
/*
FRD_RateName 45GP 45FR 40GP 20GP
------------ ----------- ----------- ----------- -----------
标准报价 4100 4000 4200 4300
退佣10 2 1 3 4
箱扣100 42 41 43 44
(3 row(s) affected)
*/
Andy__Huang 2013-08-08
- 打赏
- 举报
create table #tb(
ContainerName varchar(50),
FRD_RateName varchar(50),
FRD_Charges numeric(12)
)
insert into #tb
select '45FR','标准报价',4000
union all select '45FR','箱扣100',41
union all select '45FR','退佣10',1
union all select '45GP','标准报价',4100
union all select '45GP','箱扣100',42
union all select '45GP','退佣10',2
union all select '40GP','标准报价',4200
union all select '40GP','箱扣100',43
union all select '40GP','退佣10',3
union all select '20GP','标准报价',4300
union all select '20GP','箱扣100',44
union all select '20GP','退佣10',4
select * from #tb
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(ContainerName)+']=max(case ContainerName when '''+rtrim(ContainerName)+''' then rtrim(FRD_Charges) end)'
from #tb group by ContainerName
exec('select FRD_RateName'+@sql+'from #tb group by FRD_RateName' )
drop table #tb
/*
标准报价 4300 4200 4000 4100
退佣10 4 3 1 2
箱扣100 44 43 41 42
*/唐诗三百首 2013-08-08
- 打赏
- 举报
create table q14
(ContainerName varchar(12), FRD_RateName varchar(12), FRD_Charges int)
insert into q14
select '45FR', '标准报价', 4000 union all
select '45FR', '箱扣100', 41 union all
select '45FR', '退佣10', 1 union all
select '45GP', '标准报价', 4100 union all
select '45GP', '箱扣100', 42 union all
select '45GP', '退佣10', 2 union all
select '40GP', '标准报价', 4200 union all
select '40GP', '箱扣100', 43 union all
select '40GP', '退佣10', 3 union all
select '20GP', '标准报价', 4300 union all
select '20GP', '箱扣100', 44 union all
select '20GP', '退佣10', 4
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'max(case when ContainerName='''+ContainerName+''' then FRD_Charges else 0 end) ['+ContainerName+']'
from (select distinct ContainerName from q14) t
order by t.ContainerName desc
select @tsql='select FRD_RateName,'
+@tsql
+' from q14 group by FRD_RateName '
exec(@tsql)
/*
FRD_RateName 45GP 45FR 40GP 20GP
------------ ----------- ----------- ----------- -----------
标准报价 4100 4000 4200 4300
退佣10 2 1 3 4
箱扣100 42 41 43 44
(3 row(s) affected)
*/
