Get data from table, Using Rows as Columns.

Question

I have following data in my table.

I want to extract ProductId which has this criteria,

FieldValue = 1.0 and FieldValue = 'Y' and FieldValue = 'N'

This is not possible using following query

select * from MyTable
WHERE (FieldId = 50 AND (FieldValue BETWEEN '1.0' AND '1.0')) 
AND (FieldId = 55 AND FieldValue = 'Y') 
AND (FieldId = 60 AND FieldValue = 'N')

and I can't use query like this. This also fetch ProductId 103 and 104.

select * from MyTable
WHERE (FieldId = 50 AND (FieldValue BETWEEN '1.0' AND '1.0')) 
OR (FieldId = 55 AND FieldValue = 'Y') 
OR (FieldId = 60 AND FieldValue = 'N')

I don't know ProductId in advance. In-fact I want to extract ProductId using FieldValue criteria. I CAN'T USE ProductId in my where clause because I don't know. Only I know Is the fieldValue and FieldId.

Thanks for help!

Answer 1

What about this?

ProductId = 101 and FieldValue IN ('1.0', 'Y', 'N')

[Edit]

Maybe you can use a subquery like this?

SELECT *
FROM MyTable
WHERE ProductId = 
-- SubQuery for searching ProductId based on FieldId and FieldValue
(SELECT TOP 1 ProductId
FROM MyTable
WHERE (FieldId = 50 AND (FieldValue BETWEEN '1.0' AND '1.0'))

Answer 2

The FieldValue for a record can never have two values at once, that's why a condition like x=1 and x=2 never can be true.

You want to use or between the conditions:

ProductId = 101 and (FieldValue = '1.0' or FieldValue = 'Y' and FieldValue = 'N')

Edit:
If you have to find a ProductId with that combination of FieldId and FieldValue values, you have to do some joining:

select * from MyTable
where ProductId = (
  select ProductId
  from MyTable m
  inner join MyTable m2 on m2.ProductId = m.ProductId and m2.FieldId = 55 and FieldValue = 'Y'
  inner join MyTable m3 on m3.ProductId = m.ProductId and m3.FieldId = 60 and FieldValue = 'N'
  where FieldId = 50 and FieldValue = '1.0'
)

Answer 3

I guess you want the following:

SELECT ProductId from myTable
 WHERE ProductId IN (SELECT ProductId FROM myTable WHERE FieldId = 50 AND FieldValue = '1.0')
   AND ProductId IN (SELECT ProductId FROM myTable WHERE FieldId = 55 AND FieldValue = 'Y') 
   AND ProductId IN (SELECT ProductId FROM myTable WHERE FieldId = 60 AND FieldValue = 'N')

(You can put one of the criteria in the outer SELECT, but I guess it's easier to read this way.)

Answer 4

select distinct t1.productid
from mytable t1
inner join mytable t2 on t1.productid = t2.productid
inner join mytable t3 on t2.productid = t3.productid
where t1.fieldvalue = '1.0' and t1.fieldid = 50
and t2.fieldvalue = 'Y' and t2.fieldid = 55
and t3.fieldvalue = 'N' and t3.fieldid = 60