sql - 2 列值在单独的列中为一行

Question

我用谷歌搜索了类似的问题，但似乎很难理解所提供的解决方案。基本上，我想让两列的值相邻，而不是为相同的 ERpCusCode 分开行。我感谢一个帮助或简单的例子对于这个问题是可以实现的。

我的查询是

select distinct gg.* from
(

select cc.*,count (xc.BudgetGroupId )as total ,'' as added from  ---,COUNT (av.BudgetGroupId),COUNT (av.BudgetGroupId)
            (

            select * from (
                    SELECT distinct
                    c.ERpCusCode, c.Name,c.id as CustomerID
                            FROM LoyaltyProgramResult AS lpr 
                    JOIN Customer AS c ON lpr.CustomerId=c.Id 

                    WHERE lpr.InvoiceNo IS NULL AND lpr.[Year]='2018' AND  lpr.[Month]='8'
                    AND lpr.DistributionCenterId=1 AND c.RouteId=33 and lpr.LoyaltyProgramId=71
                    group by 
                    c.ERpCusCode,lpr.AreaSalesManagerId,
                    c.Name,c.id ,lpr.[Year],
                    lpr.[Month],lpr.DistributionCenterId,
                    c.RouteId,lpr.LoyaltyProgramId,
                    lpr.Incentive,lpr.Approve
                    )rowdetails --where  prn=1

            )cc 

left join 

( select bg.*
 from budgetgroupsales  bg
 where customerId=165 and
  qty!=100
 and bg.RouteId =33

 and MONTH(bg.InvoiceDate) = 10
 and YEAR (bg.InvoiceDate) = 2018

 )xc on cc.CustomerID =xc.CustomerId 

 group by cc.ERpCusCode,cc.AreaSalesManagerId, cc.Name,cc.CustomerID ,cc.year,cc.Month ,cc.DistributionCenterId ,cc.routeId,
 cc.LoyaltyProgramId,cc.Incentive,cc.Approve,cc.prn--,total ,added

 union 

 select cc.*,'' as total,count (xc.BudgetGroupId )as added from  ---,COUNT (av.BudgetGroupId),COUNT (av.BudgetGroupId)
        (
        select * from (
                    SELECT distinct

                    c.ERpCusCode, c.Name,c.id as CustomerID

                    FROM LoyaltyProgramResult AS lpr 
                    JOIN Customer AS c ON lpr.CustomerId=c.Id 

                    WHERE lpr.InvoiceNo IS NULL AND lpr.[Year]='2018' AND  lpr.[Month]='8'
                    AND lpr.DistributionCenterId=1 AND c.RouteId=33 and lpr.LoyaltyProgramId=71
                    group by 
                    c.ERpCusCode,lpr.AreaSalesManagerId,
                    c.Name,c.id ,lpr.[Year],
                    lpr.[Month],lpr.DistributionCenterId,
                    c.RouteId,lpr.LoyaltyProgramId,
                    lpr.Incentive,lpr.Approve
                    )rowdetails --where  prn=1

        )cc

left join 

( select bg.*
 from budgetgroupsales  bg
 where customerId=165 and
  qty!=100 and  qty=200
 and bg.RouteId =33

 and MONTH(bg.InvoiceDate) = 10
 and YEAR (bg.InvoiceDate) = 2018

 )xc on cc.CustomerID =xc.CustomerId 

 group by cc.ERpCusCode,cc.AreaSalesManagerId, cc.Name,cc.CustomerID ,cc.year,cc.Month ,cc.DistributionCenterId ,cc.routeId,
 cc.LoyaltyProgramId,cc.Incentive,cc.Approve,cc.prn--,total,added


 )as gg 
 group by ERpCusCode,AreaSalesManagerId,Name,CustomerID,year,Month
 ,DistributionCenterId,routeId,LoyaltyProgramId,Incentive,Approve,total,added,prn

当前返回数据集是

  ERpCusCode    Name CustomerID  total  added
ADLAD00001724   y     743          0          0
ADLAD00001774   x     165          0          2
ADLAD00001774   x     165         10          0

我需要什么

 ERpCusCode Name CustomerID  total  added

 ADLAD00001724  y     743          0          0

 ADLAD00001774  x     165         10          2

标签： sql

Answer 1

使用聚合函数 max() 并删除不同的 put code 和 name in group by

select gg.ERpCusCode ,gg.Name ,max(total) as total ,max(added) from
(

select cc.*,count (xc.BudgetGroupId )as total ,'' as added from  ---,COUNT (av.BudgetGroupId),COUNT (av.BudgetGroupId)
            (

            select * from (
                    SELECT distinct
                    c.ERpCusCode, c.Name,c.id as CustomerID
                            FROM LoyaltyProgramResult AS lpr 
                    JOIN Customer AS c ON lpr.CustomerId=c.Id 

                    WHERE lpr.InvoiceNo IS NULL AND lpr.[Year]='2018' AND  lpr.[Month]='8'
                    AND lpr.DistributionCenterId=1 AND c.RouteId=33 and lpr.LoyaltyProgramId=71
                    group by 
                    c.ERpCusCode,lpr.AreaSalesManagerId,
                    c.Name,c.id ,lpr.[Year],
                    lpr.[Month],lpr.DistributionCenterId,
                    c.RouteId,lpr.LoyaltyProgramId,
                    lpr.Incentive,lpr.Approve
                    )rowdetails --where  prn=1

            )cc 

left join 

( select bg.*
 from budgetgroupsales  bg
 where customerId=165 and
  qty!=100
 and bg.RouteId =33

 and MONTH(bg.InvoiceDate) = 10
 and YEAR (bg.InvoiceDate) = 2018

 )xc on cc.CustomerID =xc.CustomerId 

 group by cc.ERpCusCode,cc.AreaSalesManagerId, cc.Name,cc.CustomerID ,cc.year,cc.Month ,cc.DistributionCenterId ,cc.routeId,
 cc.LoyaltyProgramId,cc.Incentive,cc.Approve,cc.prn--,total ,added

 union 

 select cc.*,'' as total,count (xc.BudgetGroupId )as added from  ---,COUNT (av.BudgetGroupId),COUNT (av.BudgetGroupId)
        (
        select * from (
                    SELECT distinct

                    c.ERpCusCode, c.Name,c.id as CustomerID

                    FROM LoyaltyProgramResult AS lpr 
                    JOIN Customer AS c ON lpr.CustomerId=c.Id 

                    WHERE lpr.InvoiceNo IS NULL AND lpr.[Year]='2018' AND  lpr.[Month]='8'
                    AND lpr.DistributionCenterId=1 AND c.RouteId=33 and lpr.LoyaltyProgramId=71
                    group by 
                    c.ERpCusCode,lpr.AreaSalesManagerId,
                    c.Name,c.id ,lpr.[Year],
                    lpr.[Month],lpr.DistributionCenterId,
                    c.RouteId,lpr.LoyaltyProgramId,
                    lpr.Incentive,lpr.Approve
                    )rowdetails --where  prn=1

        )cc

left join 

( select bg.*
 from budgetgroupsales  bg
 where customerId=165 and
  qty!=100 and  qty=200
 and bg.RouteId =33

 and MONTH(bg.InvoiceDate) = 10
 and YEAR (bg.InvoiceDate) = 2018

 )xc on cc.CustomerID =xc.CustomerId 

 group by cc.ERpCusCode,cc.AreaSalesManagerId, cc.Name,cc.CustomerID ,cc.year,cc.Month ,cc.DistributionCenterId ,cc.routeId,
 cc.LoyaltyProgramId,cc.Incentive,cc.Approve,cc.prn--,total,added


 )as gg 
 group by gg.ERpCusCode ,gg.Name