create table erdem_fistipleri2
(
smodel varchar(50),
lfisno varchar(50),
nstoktipi int,
sfistipi char(50),
lbrutfiyat float,
lIlaveMaliyetTutar float
)
******************************************************
INSERT INTO erdem_fistipleri2 values('XXXXXXXX','9999999',2,'FA',1,166.67)
GO
UNION ALL
INSERT INTO mytest values('159 010 100','9999999',4,'HA',100,0)
GO
UNION ALL
INSERT INTO mytest values('159 010 106','9999999',4,'HA',500,0)
GO
UNION ALL
INSERT INTO mytest values('159 010 105','9999999',4,'HA',400,0)
GO
UNION ALL
INSERT INTO mytest values('YYYYYYYY','9999999',2,'FA',5,833.33)
GO
***************************************************
SELECT m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT
INTO #subresults
FROM erdem_fistipleri2 m
CROSS APPLY
(
SELECT smodel,lbrutfiyat
FROM erdem_fistipleri2 c
WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'
SELECT lfisno,m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/(select sum(lbrutfiyat) from erdem_fistipleri2 where sfistipi='HA') AS RESULT
INTO #subresults
FROM erdem_fistipleri2 m
CROSS APPLY
(
SELECT smodel,lbrutfiyat
FROM erdem_fistipleri2 c
WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'
SELECT lfisno,m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/(select sum(lbrutfiyat) from erdem_fistipleri2 where sfistipi='HA') AS RESULT
INTO #subresults
FROM erdem_fistipleri2 m
CROSS APPLY
(
SELECT smodel,lbrutfiyat
FROM erdem_fistipleri2 c
WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'
declare @val int
set @val=1000
SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/@val as result
from erdem_fistipleri2 as one join
(
select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2 where sfistipi='HA' group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'
-- step 1: build a intermediate table to get the data in a more relational format
SELECT m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT
INTO #subresults
FROM erdem_fistipleri2 m
CROSS APPLY
(
SELECT smodel,lbrutfiyat
FROM erdem_fistipleri2 c
WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'
SELECT *
FROM #subresults
--method 1
declare @val int
set @val=1000
SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/@val as result
from erdem_fistipleri2 as one join
(
select lfisno,lbrutfiyat as tot from erdem_fistipleri2 where sfistipi='HA' --group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'
GO
--method2
declare @val int
set @val=1000
SELECT one.smodel,one.lfisno,one.lIlaveMaliyetTutar * two.tot/@val as result
from erdem_fistipleri2 as one join
(
select lfisno,sum(lbrutfiyat) as tot from erdem_fistipleri2 where sfistipi='HA' group by lfisno
) as two on one.lfisno=two.lfisno where one.sfistipi='FA'
GO
create table erdem_fistipleri2
(
smodel varchar(50),
lfisno varchar(50),
nstoktipi int,
sfistipi char(50),
lbrutfiyat float,
lIlaveMaliyetTutar float
)
******************************************************
INSERT INTO erdem_fistipleri2 values('XXXXXXXX','9999999',2,'FA',1,166.67)
GO
UNION ALL
INSERT INTO erdem_fistipleri2 values('159 010 100','9999999',4,'HA',100,0)
GO
UNION ALL
INSERT INTO erdem_fistipleri2 values('159 010 106','9999999',4,'HA',500,0)
GO
UNION ALL
INSERT INTO erdem_fistipleri2 values('159 010 105','9999999',4,'HA',400,0)
GO
UNION ALL
INSERT INTO erdem_fistipleri2 values('YYYYYYYY','9999999',2,'FA',5,833.33)
GO
-- step 1: build a intermediate table to get the data in a more relational format
SELECT m.lfisno,m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT
INTO #subresults
FROM erdem_fistipleri2 m
CROSS APPLY
(
SELECT smodel,lbrutfiyat
FROM erdem_fistipleri2 c
WHERE sfistipi='HA'
)c
WHERE m.sfistipi='FA'
SELECT *
FROM #subresults
-- build the dynamic cross tab query
DECLARE
@SQL1 NVARCHAR(4000),
@SQL2 NVARCHAR(4000),
@SQL3 NVARCHAR(4000)
SELECT @SQL1 = 'SELECT smodel'+CHAR(10)
--===== Create the "Mostly Static" section of the code
SELECT @SQL3 = ' FROM #subresults group by smodel'
--===== The "Hard" Part - Concatenation to Form the Date Columns
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' ,SUM(CASE WHEN col = ' + QUOTENAME(s.col,'''')
+ ' THEN result ELSE 0 END) AS [' + s.col + ']' + CHAR(10)
FROM #subresults s
GROUP BY col
--===== Print the Dynamic SQL (uncomment for troubleshooting
-- PRINT @SQL1 + @SQL2 + @SQL3
--===== Execute the Dynamic SQL to create the desired report
EXEC (@SQL1 + @SQL2 + @SQL3)
explain more
what is your expected results?