Splitting records based on qty with calculation

Transact-SQL
1

I want to transform table1 to table 2, you can see qty is 4 so it will convert into four rows with Item no for first two rwo 10, 11 and for third and four row item no with 20 and 21.
p1 of the second and forth row is addition of CI and OC.
p1 for 1 and 2 row will be CP - P1

Table 1

ItemNo Name Qty ItemNo P1 CI OC CP
1 Pen 4 1000 200 10000

Table 2

ItemNo Name Qty ItemNo P1 CI OC CP
1 Pen 1 10 8800
1 Pen 1 11 1200 1000 200 10000
1 Pen 1 20 8800
1 Pen 1 21 1200 1000 200 10000
2

hi

hope this helps !! :slight_smile:

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

3

What do you use to determine the value of ItemNo should be after decomposing table 1. How do you arrive at those values?

The following should get you started but you need to explain the above questions

image

create table #pnasz(ItemNo int, Name nvarchar(50), QTY int, 
ItemNo_ int, P1 int, CI int, OC int, CP int)

insert into #pnasz
select 1, 'Pen', 4, '', null, 1000, 200, 10000

declare @top  int ;
select @top = max(qty) from #pnasz


-- Numbers table
CREATE  TABLE #Numbers (NumberValue SMALLINT);

INSERT  INTO #Numbers
SELECT  TOP (@top)
        ROW_NUMBER() OVER (ORDER BY object_id)
FROM    sys.objects;

select ItemNo, 
       Name, 
	   QTY/QTY as QTY, 
	   ItemNo,  
       case 
	      when NumberValue % 2 <> 0 
		  then CP - (CI + OC)  
		  else (CI + OC)  
		end as P1, 
       case 
	      when NumberValue % 2 <> 0 
		  then null 
		  else CI 
		end as CI, 
		case 
	      when NumberValue % 2 <> 0 
		  then null 
		  else OC 
		end as OC, 
		   case 
	      when NumberValue % 2 <> 0 
		  then null 
		  else CP 
		end as CP, 
		NumberValue
  FROM #pnasz
  JOIN #Numbers
    ON QTY >= NumberValue


drop table #pnasz
drop table #Numbers
4

What do you want to do if the Qty = 10,000?

5

If you notice historically OP does not respond once an answer is given

6

NP. Op's loss there, though. :wink:

1 Like