Ok - solution to second problem:
First run this:
select DPNo,
ItemName +'_'+col col,
MatQty
from(
select
DPNo,
ItemName,
cast(MatQty as numeric(10, 2)) mtqty,
cast(Rate as numeric(10, 2)) rate
from
Vw_DailyEntry
) src
unpivot(
MatQty
for col in (rate,mtqty)
) unpiv
Notice the names in [col] - These are the column names available in the pivot.
Some of these names start with a digit. That's ok - bracket them:
for col in ([10prdp_rate], [10prdp_mtqty], [10p_rate], [10p_mtqty], [D-Clamp_rate], [D-Clamp_mtqty], [D_C_rate], [D_C_mtqty])
Update these and the select columns to get some values
PS: this was my test data:
declare @Vw_DailyEntry table (
DPNo int identity(1,1) primary key not null,
ItemName nvarchar(max) not null,
MatQty numeric(10, 2) not null,
Rate numeric(10, 2) not null)
INSERT INTO @Vw_DailyEntry(
ItemName,
MatQty,
Rate
)
values
('10prdp',1.0,1.0),
('10prdp',2.1,2.1),
('10prdp',3.2,3.2),
('D-Clamp',4.3,4.3),
('10p',5.4,5.4),
('D_C',6.5,6.5),
('10prdp',7.6,7.6),
('D_C',8.7,8.7),
('D_C',9.8,9.8),
('D-Clamp',10.9,10.9),
('10prdp',11.10,11.10),
('10p',12.11,12.11),
('10p',13.12,13.12),
('D_C',14.13,14.13),
('D-Clamp',15.14,15.14),
('D_C',16.15,16.15),
('10p',17.16,17.16),
('D_C',18.17,18.17),
('10p',19.18,19.18),
('D_C',20.19,20.19)