CODEWITHSUNDEEP
sqlpivot
kobalt
kobalt

Reputation: 1

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

Upvotes: 0

Views: 81

Answers (1)

MikkaRin
MikkaRin

Reputation: 3084

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

Upvotes: 1

Related Questions