Ilmu Sql

Saya punya table #x yang isinya seperti berikut

Saya ingin mengganti hasilnya dengan data seperti berikut:




berikut querynya

select * from (
    select idstructure, idcovjob1, (select top 1 idcovjob from #x where idstructure=a.idstructure and idcovjob<>a.idcovjob1) idcovjob2
    from (
        select distinct idstructure, (select top 1 idcovjob from #x where idstructure=a.idstructure) idcovjob1 from #x a
    ) a
   
    union
   
    select b.idstructure, idcovjob1, c.idcovjob idcovjob2 from (
        select idstructure, idcovjob1, (select top 1 idcovjob from #x where idstructure=a.idstructure and idcovjob<>a.idcovjob1) idcovjob2
        from (
            select distinct idstructure, (select top 1 idcovjob from #x where idstructure=a.idstructure) idcovjob1 from #x a
        ) a
    ) b
    inner join #x c on (b.idstructure=c.idstructure and idcovjob<>b.idcovjob1 and idcovjob<>b.idcovjob2)
) a
order by 1

alter function fincentivetrade2013_sales()
returns @sales
table
(
    iddistsales int, idproduct int, year int, month int, idcovjob varchar(20), product varchar(100), outlet  varchar(300),
    salesgross float, panel float, pbf float, oth float, diskon float, salesnet float
)
as
begin
   DECLARE @trdistributorsales TABLE
        (
            year int,
            month int,
            iddistsales bigint,
            price float,
            channelcode varchar(20),
            idoutlet int,
            idchannel int,
            idproduct int,
            prins float, PrinsHNA float,
            PrinXtra float, PrinPnel float, PrinE float
        )
       
        insert into @trdistributorsales(year, month, iddistsales, idproduct, idoutlet, prins, PrinsHNA, PrinXtra, PrinPnel, PrinE)
        select year(trxdate) year, month(trxdate) month, iddistsales, idproduct, idoutlet, prins, PrinsHNA, PrinXtra, PrinPnel, PrinE from trdistributorsales
        where year(trxdate)=2013 and month(trxdate)=1
       
        insert into @sales
        select iddistsales, idproduct, year, month, idcovjob, product, outlet, sum(salesgross) salesgross, sum(panel) panel, sum(pbf) pbf, sum(subtotal) oth, sum(diskon) diskon,
        sum(subtotal) + sum(panel) + sum(pbf) + sum(diskon) salesnet
        from (
            select year, month, idcovjob, iddistsales, a.idproduct, b.name product, a.idoutlet, c.name outlet,
            subtotal salesgross,
            case when tender=1 or panel=1 or pbf=1 then 0 else subtotal end subtotal,
            case when tender=1 then 0 when panel=1 then subtotal*0.5 else 0 end panel,
            case when tender=1 then 0 when pbf=1 then subtotal*0.25 else 0 end pbf,
            case when tender=1 then 0 when panel=1 then diskon/2 when pbf=1 then diskon/4 else diskon end diskon,
            tender   
            from (
                select year, month, c.idcovjob, a.iddistsales, a.idproduct, a.idoutlet,
                subtotal,
                case when channelcode in('08','09','8A', '8B') then 1 else 0 end tender,
                case when channelcode in('04','07','10') or a.prinpnel<>0 then 1 else 0 end panel,   
                case when d.outletid is not null then 1 else 0 end pbf,
                ( (isnull(a.Prins,0)*isnull(price,0)) + isnull(a.PrinsHNA,0) + isnull(a.PrinXtra,0) + isnull(a.PrinPnel,0) + isnull(a.PrinE,0) ) diskon
                from @trdistributorsales a
                inner join trclaimsales c on (a.iddistsales=c.iddistsales)
                left join moutletincentive d on (a.idoutlet=d.outletid)
                inner join mcoverjob e on (c.idcovjob=e.idcovjob)       
                where e.idsubcat=31 --and year(a.trxdate)=2013
                and claimtype='A' and isrelease='F' --and c.idcovjob=5435 --and a.iddistsales=10587390
            ) a
            inner join mproduct b on (a.idproduct=b.idprod)
            inner join moutlet c on (a.idoutlet=c.idoutlet)
            --where tender=0
        ) a
        where year=2013 --and month=1
        group by iddistsales, idproduct, year, month, idcovjob, product, outlet       

    return
end

alter function tes()
returns
@datas table
(
  nik varchar(20),
  officialname varchar(100)
)
as
begin
    DECLARE @data TABLE
    (
        nik varchar(20),
        officialname varchar(100)
    )

    INSERT INTO @datas (nik, officialname)
    select nik, officialname from memployee
   
    return
   
end

alter Function FN()
returns
@Names table(name varchar(200))
as
begin

   
    insert into @Names
    select officialname from memployee

    return
end