Require expected output ,in row

Question

Require expected output ,in row

Analyst_SQL 3,576

Below is data ,i want to convert Bweight into Row as display in image.

CREATE TABLE #item (ItemCode int,ItemName varchar(max))  
  
CREATE TABLE #DistpatchM(DID int,E_Date date)  
CREATE TABLE #Distpatch_BD(ID int,DID int,Itemcode int,DisQTY int,Bweight int)  
     
INSERT INTO #item VALUES(1001, 'A')  
INSERT INTO #item VALUES(1002, 'B')  
INSERT INTO #item VALUES(1003, 'C')  
INSERT INTO #item VALUES(1004, 'D')  
INSERT INTO #item VALUES(1005, 'E')  
INSERT INTO #item VALUES(1006, 'F')  
INSERT INTO #item VALUES(1007, 'G')  
    
  
  
  
INSERT INTO #DistpatchM values(111,'2019-05-28')  
INSERT INTO #DistpatchM values(112,'2018-05-29')  
               
INSERT INTO #Distpatch_BD values(1,1001,111,1 ,500)  
INSERT INTO #Distpatch_BD values(2,1002,111 ,1,600)  
INSERT INTO #Distpatch_BD values(3,1003,111 ,1,800)  
INSERT INTO #Distpatch_BD values(4,1006,112,1,900)  
INSERT INTO #Distpatch_BD values(5,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(6,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(7,1006,112,1,900)  
INSERT INTO #Distpatch_BD values(8,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(9,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(10,1006,112,1,900)  
INSERT INTO #Distpatch_BD values(11,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(12,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(13,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(14,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(14,1001,112,1,400)  

Select  #item.ItemName,#Distpatch_BD.DisQTY,#Distpatch_BD.Bweight  
From #Distpatch_BD inner join #item on #item.itemcode=#Distpatch_BD.itemcode  
where #Distpatch_BD.DID=112

Olaf Helper 47,621 Reputation points

2021-02-18T11:41:17.447+00:00

Can you explain the logic for the expected result, please?
Analyst_SQL 3,576 Reputation points

2021-02-18T15:49:06.453+00:00

This is not any logic,just i want to display Bweight from #Distpatch_BD into row,but have 10 columns as shown in image.
Yitzhak Khabinsky 27,196 Reputation points

2021-02-18T15:55:21.887+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Viorel 127K Reputation points

2021-02-18T16:11:15.953+00:00

Why Bweight 500 is not displayed?
Analyst_SQL 3,576 Reputation points

2021-02-19T04:35:23.39+00:00

i want to just look #DistpatchM DID 112 data ,which exit in both table
Analyst_SQL 3,576 Reputation points

2021-02-19T04:58:31.97+00:00

Select #item.ItemName,#Distpatch_BD.DisQTY,#Distpatch_BD.Bweight
From #Distpatch_BD inner join #item on #item.itemcode=#Distpatch_BD.itemcode
where #Distpatch_BD.DID=112

Above query is displaying Data into Column ,but i want into Row Just Bweight
Analyst_SQL 3,576 Reputation points

2021-02-19T05:01:31.61+00:00

it is Displaying Data into Column ,But i want Disqty Sum and Begwight into row
Select #item.ItemName,#Distpatch_BD.DisQTY,#Distpatch_BD.Bweight
From #Distpatch_BD inner join #item on #item.itemcode=#Distpatch_BD.itemcode
where #Distpatch_BD.DID=112
Viorel 127K Reputation points

2021-02-19T14:11:36.047+00:00

Why Grand Total is 11, not 12?
Analyst_SQL 3,576 Reputation points

2021-02-19T14:50:49.447+00:00

Oh sorry ,yes it will be 12

Answer accepted by question author

EchoLiu-MSFT 14,626

According to Viorel-1's suggestion, the modified code should be::

 CREATE TABLE #item (ItemCode int,ItemName varchar(max))  
 CREATE TABLE #DistpatchM(DID int,E_Date date)  
 CREATE TABLE #Distpatch_BD(ID int, Itemcode int, DID int, DisQTY int, Bweight int)   
  
  
  
 INSERT INTO #item VALUES(1001, 'A')   
 INSERT INTO #item VALUES(1002, 'B')  
 INSERT INTO #item VALUES(1003, 'C')  
 INSERT INTO #item VALUES(1004, 'D')  
 INSERT INTO #item VALUES(1005, 'E')  
 INSERT INTO #item VALUES(1006, 'F')  
 INSERT INTO #item VALUES(1007, 'G')  
  
 INSERT INTO #DistpatchM values(111,'2019-05-28')  
 INSERT INTO #DistpatchM values(112,'2018-05-29')  
                   
 INSERT INTO #Distpatch_BD values(1,1001,111,1 ,500)  
 INSERT INTO #Distpatch_BD values(2,1002,111 ,1,600)  
 INSERT INTO #Distpatch_BD values(3,1003,111 ,1,800)  
 INSERT INTO #Distpatch_BD values(4,1006,112,1,900)  
 INSERT INTO #Distpatch_BD values(5,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(6,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(7,1006,112,1,900)  
 INSERT INTO #Distpatch_BD values(8,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(9,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(10,1006,112,1,900)  
 INSERT INTO #Distpatch_BD values(11,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(12,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(13,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(14,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(14,1001,112,1,400)  
  
  
  
;with I as  
 (  
     select *  
     from #Distpatch_BD  
     where DID in (112)  
 ),  
 N as  
 (  
     select *, row_number() over (partition by itemcode order by ID) - 1 as n  
     from I  
 ),  
 P as  
 (  
     select *, cast(Bweight as varchar(max)) as BweightT, n % 10 + 1 as c, n / 10 + 1 as r  
     from N  
 ),  
 G as  
 (  
     select *,   
         isnull(lead(BweightT, 0) over (partition by itemcode order by ID), '') as [1],  
         isnull(lead(BweightT, 1) over (partition by itemcode order by ID), '') as [2],  
         isnull(lead(BweightT, 2) over (partition by itemcode order by ID), '') as [3],  
         isnull(lead(BweightT, 3) over (partition by itemcode order by ID), '') as [4],  
         isnull(lead(BweightT, 4) over (partition by itemcode order by ID), '') as [5],  
         isnull(lead(BweightT, 5) over (partition by itemcode order by ID), '') as [6],  
         isnull(lead(BweightT, 6) over (partition by itemcode order by ID), '') as [7],  
         isnull(lead(BweightT, 7) over (partition by itemcode order by ID), '') as [8],  
         isnull(lead(BweightT, 8) over (partition by itemcode order by ID), '') as [9],  
         isnull(lead(BweightT, 9) over (partition by itemcode order by ID), '') as [10]  
     from P  
 ),  
 R as  
 (  
     select *   
     from G   
     where c = 1  
 ),  
 F as  
 (  
     select   
         case r when 1 then t.ItemName else '' end as Item,   
         case r when 1 then cast((select count(*) from I where itemcode = R.itemcode) as varchar(max)) else '' end as QTY,  
         case when lead(R.itemcode) over (partition by R.itemcode order by r) is null   
             then cast((select sum(Bweight) from I where itemcode = R.itemcode) as varchar(max)) else '' end as Total,  
         R.*  
     from R  
     inner join #item t on t.ItemCode = R.itemcode  
 ),  
 GT as  
 (  
     select   
         cast(count(*) as varchar(max)) as GrandTotalQTY,   
         cast(sum(I.Bweight) as varchar(max)) as GrandTotalBWeight  
     from I  
 ),  
 E as  
 (  
     select distinct itemcode  
     from I  
 ),  
 U as  
 (  
     select itemcode, Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2  
     from F  
     union all  
     select itemcode, '', '', '', '', '', '', '', '', '', '', '', '', '', 2, 1  
     from E  
     union all  
     select NULL, 'Grand Total', GrandTotalQTY, '', '', '', '', '', '', '', '', '', '', GrandTotalBWeight, 1, 2  
     from GT  
 )  
 select Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total  
 from U  
 order by ord2, itemcode, ord1

Output:

Viorel 127K Reputation points

2021-02-23T13:37:13.627+00:00

Is this really acceptable?
Analyst_SQL 3,576 Reputation points

2021-02-23T15:30:43.42+00:00

yes ,i want this output
Viorel 127K Reputation points

2021-02-23T21:03:41.377+00:00

I meant something else, but I do not insist.

2 additional answers

Your answer

Olaf Helper 47,621 Reputation points

2021-02-18T11:41:17.447+00:00

Can you explain the logic for the expected result, please?
Analyst_SQL 3,576 Reputation points

2021-02-18T15:49:06.453+00:00

This is not any logic,just i want to display Bweight from #Distpatch_BD into row,but have 10 columns as shown in image.
Yitzhak Khabinsky 27,196 Reputation points

2021-02-18T15:55:21.887+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Viorel 127K Reputation points

2021-02-18T16:11:15.953+00:00

Why Bweight 500 is not displayed?
Analyst_SQL 3,576 Reputation points

2021-02-19T04:35:23.39+00:00

i want to just look #DistpatchM DID 112 data ,which exit in both table
Analyst_SQL 3,576 Reputation points

2021-02-19T04:58:31.97+00:00

Select #item.ItemName,#Distpatch_BD.DisQTY,#Distpatch_BD.Bweight
From #Distpatch_BD inner join #item on #item.itemcode=#Distpatch_BD.itemcode
where #Distpatch_BD.DID=112

Above query is displaying Data into Column ,but i want into Row Just Bweight
Analyst_SQL 3,576 Reputation points

2021-02-19T05:01:31.61+00:00

it is Displaying Data into Column ,But i want Disqty Sum and Begwight into row
Select #item.ItemName,#Distpatch_BD.DisQTY,#Distpatch_BD.Bweight
From #Distpatch_BD inner join #item on #item.itemcode=#Distpatch_BD.itemcode
where #Distpatch_BD.DID=112
Viorel 127K Reputation points

2021-02-19T14:11:36.047+00:00

Why Grand Total is 11, not 12?
Analyst_SQL 3,576 Reputation points

2021-02-19T14:50:49.447+00:00

Oh sorry ,yes it will be 12
Viorel 127K Reputation points

2021-02-23T13:37:13.627+00:00

Is this really acceptable?
Analyst_SQL 3,576 Reputation points

2021-02-23T15:30:43.42+00:00

yes ,i want this output
Viorel 127K Reputation points

2021-02-23T21:03:41.377+00:00

I meant something else, but I do not insist.

Answer 1

Viorel 127K

Check a long unoptimised solution too:

;
with I as
(
    select *
    from #Distpatch_BD
    where itemcode in (112)
),
N as
(
    select *, row_number() over (partition by DID order by ID) - 1 as n
    from I
),
P as
(
    select *, cast(Bweight as varchar(max)) as BweightT, n % 10 + 1 as c, n / 10 + 1 as r
    from N
),
G as
(
    select *, 
        isnull(lead(BweightT, 0) over (partition by DID order by ID), '') as [1],
        isnull(lead(BweightT, 1) over (partition by DID order by ID), '') as [2],
        isnull(lead(BweightT, 2) over (partition by DID order by ID), '') as [3],
        isnull(lead(BweightT, 3) over (partition by DID order by ID), '') as [4],
        isnull(lead(BweightT, 4) over (partition by DID order by ID), '') as [5],
        isnull(lead(BweightT, 5) over (partition by DID order by ID), '') as [6],
        isnull(lead(BweightT, 6) over (partition by DID order by ID), '') as [7],
        isnull(lead(BweightT, 7) over (partition by DID order by ID), '') as [8],
        isnull(lead(BweightT, 8) over (partition by DID order by ID), '') as [9],
        isnull(lead(BweightT, 9) over (partition by DID order by ID), '') as [10]
    from P
),
R as
(
    select * 
    from G 
    where c = 1
),
F as
(
    select 
        case r when 1 then t.ItemName else '' end as Item, 
        case r when 1 then cast((select count(*) from I where DID = R.DID) as varchar(max)) else '' end as QTY,
        case when lead(DID) over (partition by DID order by r) is null 
            then cast((select sum(Bweight) from I where DID = R.DID) as varchar(max)) else '' end as Total,
        R.*
    from R
    inner join #item t on t.ItemCode = R.DID
),
GT as
(
    select 
        cast(count(*) as varchar(max)) as GrandTotalQTY, 
        cast(sum(I.Bweight) as varchar(max)) as GrandTotalBWeight
    from I
),
E as
(
    select distinct DID
    from I
),
U as
(
    select DID, Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2
    from F
    union all
    select DID, '', '', '', '', '', '', '', '', '', '', '', '', '', 2, 1
    from E
    union all
    select NULL, 'Grand Total', GrandTotalQTY, '', '', '', '', '', '', '', '', '', '', GrandTotalBWeight, 1, 2
    from GT
)
select Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total
from U
order by ord2, DID, ord1

Analyst_SQL 3,576 Reputation points

2021-02-19T20:20:19.643+00:00

Hi @Viorel ,

Your query is working fine,but you used Codeitem instead of DID and DID is used instead of Codeitem,

Please make it change where you used DID ,replace with Codeitem and where you used codeitem replace with DID.

Please
Analyst_SQL 3,576 Reputation points

2021-02-20T05:17:17.9+00:00

Thanks ALot @Viorel
Viorel 127K Reputation points

2021-02-20T09:02:39.527+00:00

Probably you should fix the definition of your table, replacing

CREATE TABLE #Distpatch_BD(ID int,DID int,Itemcode int,DisQTY int,Bweight int)

with

CREATE TABLE #Distpatch_BD(ID int, Itemcode int, DID int, DisQTY int, Bweight int)

Then replace all of DID with Itemcode (or R.Itemcode in some cases) in the query, keeping the first one unchanged: ‘where DID in (112)’.
Analyst_SQL 3,576 Reputation points

2021-02-20T11:40:09.49+00:00

i have change it ,but column total is not coming ,as i asked in new question.

https://learn.microsoft.com/en-us/answers/questions/281236/row-total-is-not-displaying.html

Viorel 127K

The output of above script, with and without modifications, includes the Total column:

/*

Item        QTY      1       2       3       4       5       6       7       8       9       10    Total
A             1    400                                                                               400

F            11    900    4500     650     900    4500     650     900    4500     650     4500    
                   650                                                                             23300

Grand Total  12                                                                                    23700

*/

Probably you have other script and data.

Analyst_SQL 3,576 Reputation points

2021-02-20T13:55:29.103+00:00

yes your provided solution, is working 100% fine. Data is same ,just i changed field names
Analyst_SQL 3,576 Reputation points

2021-02-20T14:03:36.62+00:00

as you can see in image just last row total is coming

Answer 2

EchoLiu-MSFT 14,626

;with cte
as(Select  i.ItemName,d.DisQTY,d.Bweight
From #Distpatch_BD d
inner join #item i
on i.itemcode=d.DID
where d.Itemcode=112)
,cte2 as(select *,row_number() over(partition by ItemName order by ItemName) rr 
         from cte)
,cte3
as(select 'B' Item,* from (select rr,Bweight from cte2 where ItemName='F') as t  
   pivot (max(Bweight) for rr in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])) as p
   union all
   select 'A' Item,*,null,null,null,null,null,null,null,null,null,null from (select rr,Bweight from cte2 where ItemName='A') as t  
   pivot (max(Bweight) for rr in ([1])) as p)

select * from cte3 
order by Item

Analyst_SQL 3,576

as you used hardcore

     as(select 'B' Item,* from (select rr,Bweight from cte2 where ItemName='F') as t  
        pivot (max(Bweight) for rr in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])) as p
        union all
        select 'A' Item,*,null,null,null,null,null,null,null,null,null,null from (select rr,Bweight from cte2 where ItemName='A') as t  
        pivot (max(Bweight) for rr in ([1])) as p)

i do not want this,it pick from Database ,second thing is ,qty is not coming

Share via

Require expected output ,in row

2 additional answers

Your answer