MSSQL列转行的问题

Question

有下面这样的数据
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之类的是动态的，
求高手帮忙啊。。

Answer 1

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 descselect @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)
*/

Answer 2

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',4select * from #tbdeclare @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
*/

Answer 3

方法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 descselect @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)
*/

Answer 4

其实这个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

Answer 5

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','退佣',4SELECT DISTINCT ContainerName INTO #cu FROM #cu1declare @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)

Answer 6

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', 4create 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)
*/

Answer 7

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',4declare @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)

Answer 8

方法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', 4create 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)
*/

Answer 9

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',4DECLARE @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

Answer 10

@
我想问一下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

Answer 11

@ap0405140
不知道这一列FRD_RateType是不是也可以加到这里面的呀

Answer 12

#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
*/