kobalt kobalt - 1 month ago 6
SQL Question

sql non aggregate pivot

My query is:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column

SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(OzellikAdi)
FROM (SELECT DISTINCT OzellikAdi FROM KategoriDuzYazilar where KategoriId=6864) AS Courses
print @ColumnName

SET @DynamicPivotQuery =
N'Select * from(select i.IlanId,iduz.Deger,OzellikAdi from Ilan i
join Kategori k on i.KategoriId=k.KategoriId
join KategoriDuzYazilar kduz on kduz.KategoriId=k.KategoriId
join IlanDuzYazilar iduz on iduz.IlanId=i.IlanId
where i.KategoriId=6864
)piv
PIVOT (MAX(Deger) for OzellikAdi in(' + @ColumnName + ')) AS PVTTable'
print @DynamicPivotQuery
EXEC sp_executesql @DynamicPivotQuery


OzellikAdi is property of Ilan,Deger is integer value of OzellikAdi and values of in different tables

and result this

IlanId Ada No Pafta No Parsel No
426 9 9 9
433 24 24 24
434 210 210 210
435 775 775 775


but i dont want to aggregate and i have to get this result


IlanId Ada No Pafta No Parsel No
426 5 7 9
433 13 16 24
434 210 114 5
435 775 0 45


KategoriId is variable

http://sqlfiddle.com/#!3/3d158/1

Answer

http://sqlfiddle.com/#!3/3d158/26

The proplem was in that query

select i.IlanId,iduz.Deger,OzellikAdi from Ilan i
join Kategori k on i.KategoriId=k.KategoriId
join KategoriDuzYazilar kduz on kduz.KategoriId=k.KategoriId
join IlanDuzYazilar iduz on iduz.IlanId=i.IlanId 
and kduz.Id=iduz.KATEGORIDUZYAZILARID
where i.KategoriId=6864

you've forgot to set relationship between IlanDuzYazilar and KategoriDuzYazilar tables

Comments