SQLTeam.com | Weblogs | Forums

Querying with same column for different conditions



Have a speciification table and needed products which is Colored White and Shape is either round or rectangle. Tried with a self join query,would like to know if any other method to yield the expected result .Using sql 2012 version.

Declare @specTable table(prodID int,prodName varchar(30),specName varchar(100), specValue varchar(100))

Insert into @specTable values





			FROM @specTable  T1
				INNER JOIN @specTable T2 ON T1.prodID = T2.prodID 
				(  t1.SpecValue IN ('White')
				(  t2.SpecValue IN ('Round','Rectangle')

	group by T1.prodID

Select * from @specTable

Expected result: Prod1,Prod3,Prod4


Something like:

select prodid
  from @spectable
 group by prodid
 having sum(case when specname='Color' and specvalue='White' then 1 else 0 end)>0
    and sum(case when specname='Shape' and specvalue in ('Round','Rectangle') then 1 else 0 end)>0


I tried this piece of code but the Pivot table does not give the required output . Can anyone correct this code::


SELECT @ColumnName= ISNULL(@ColumnName + ',','')+ QUOTENAME(ProdName)
FROM (SELECT DISTINCT ProdName FROM #temp) AS ProductName

SET @DynamicPivotQuery =
N'SELECT SpecName, ' + @ColumnName + '
FROM #temp
FOR ProdName IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery


Thanks @bitsmed.
If using a subquery to call a userdefined function to make the shape filters comma separated after dynamic selection
in the above code, it shows message subquery not allowed inside aggregate.


Sounds like a new topic.


  • finish this topic (solve)
  • create new topic providing:
  • table definition in the form of create statement
  • sample data in the form of insert statement
  • the query you go so far
  • the error you get
  • the output you expect (from the sample data you provide)


No It is same one problem. I was trying using pivot . I just replaced @specTable with my table name #temp but could not find the required solution. How do I correct it .


Actually I was answering @razeena second message, but now you mention it, it also applies your message -> It is a new topic, although you're using same table.

Anyway, correct your set statement like this, and I think you'll get the result you're seeking:

SET @DynamicPivotQuery = 
 N'SELECT SpecName, ' + @ColumnName + '
 FROM (SELECT ProdName,SpecName,SpecValue
         FROM #temp
      ) AS T
 FOR ProdName IN (' + @ColumnName + ')) AS PVTTable'