SQLTeam.com | Weblogs | Forums

Querying with same column for different conditions

sql2012

#1

Hi,
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
(1,'Sink1','Shape','Round')
,(1,'Sink1','Color','White')
,(1,'Sink1','Style','Pedestal')

,(2,'Sink2','Shape','Square')
,(2,'Sink2','Color','Black')
,(2,'Sink2','Style','Wallmount')

,(3,'Sink3','Shape','Round')
,(3,'Sink3','Color','White')
,(3,'Sink3','Style','Pedestal')


,(4,'Sink4','Shape','Rectangle')
,(4,'Sink4','Color','White')
,(4,'Sink4','Style','Pedestal')

,(5,'Sink5','Shape','Round')
,(5,'Sink5','Color','green')
,(5,'Sink5','Style','Pedestal')



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

	group by T1.prodID



Select * from @specTable

Expected result: Prod1,Prod3,Prod4


#2

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
;

#3

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

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

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

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

EXEC sp_executesql @DynamicPivotQuery


#4

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.


#5

Sounds like a new topic.

Please:

  • 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)

#6

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 .


#7

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
 PIVOT(Min(SpecValue)
 FOR ProdName IN (' + @ColumnName + ')) AS PVTTable'