Get data from table, Using Rows as Columns

Question

I have following data in my table.

alt text http://img26.imageshack.us/img26/3746/productfield.png

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

alt text http://img690.imageshack.us/img690/16/productfieldresult.png

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!

I only need ProductId matching criteria I have mentioned. There is no need to re arange columns in a row. — Kashif
– Kashif, Commented Nov 7, 2009 at 14:21
I feel that dotjoe solution is much more relevant because u want to perform the search based on the fieldvalue criteria and not on both fieldvalue & fieldid. (: — priyanka.sarkar
– priyanka.sarkar, Commented Nov 7, 2009 at 14:55
Indeed, that's a good question: Do you want to filter only on FieldValue or on (FieldId, FieldValue)-combinations? Your question sounds more like the former, but your SQL examples and "common sense" lead towards the second... — Heinzi
– Heinzi, Commented Nov 7, 2009 at 15:15
Heinzi got correct sense from my SQL Examples. I want that particular ProductId which fulfill my criteria. @dotjoe is also correct but I accepted @Heinzi because he comes with my solution a little earlier. I am thankful to all people who answered my question. — Kashif
– Kashif, Commented Nov 7, 2009 at 19:27

dotjoe · Accepted Answer · 2009-11-08 17:53:58Z

1

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

edited Nov 8, 2009 at 17:53

answered Nov 7, 2009 at 14:45

dotjoe

27.1k5 gold badges67 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

priyanka.sarkar Over a year ago

good answer...exactly what the OP's criteria is... only on fieldvalue and not on FieldId..

priyanka.sarkar Over a year ago

but you forgot to add mytable in the inner joins. inner join mytable t2 on t1.productid = t2.productid inner join mytablet t3 on t2.productid = t3.productid

Heinzi · Accepted Answer · 2009-11-07 14:37:07Z

0

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

answered Nov 7, 2009 at 14:37

Heinzi

173k61 gold badges386 silver badges555 bronze badges

Comments

Jhonny D. Cano -Leftware- · Accepted Answer · 2009-11-07 14:36:17Z

0

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

edited Nov 7, 2009 at 14:36

answered Nov 7, 2009 at 14:22

Jhonny D. Cano -Leftware-

18.1k15 gold badges84 silver badges103 bronze badges

3 Comments

Kashif Over a year ago

ProductId is the value which I want to get using FieldValue criteria. So I can't use PrdouctId = 101

Jhonny D. Cano -Leftware- Over a year ago

Not so obviously. Your question is not so obvious, indeed. A field in a row will never have two values at once, as Guffa stated. My "IN" solution is equivalent to him's

Jhonny D. Cano -Leftware- Over a year ago

@Muhammad check it again... this time using a subquery

Guffa · Accepted Answer · 2009-11-07 14:50:54Z

0

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

edited Nov 7, 2009 at 14:50

answered Nov 7, 2009 at 14:27

Guffa

703k112 gold badges760 silver badges1k bronze badges

2 Comments

Kashif Over a year ago

Thanks, I don't know ProductId in advance so I can't use it in my Where. Any solution using temp table etc.

Guffa Over a year ago

I see. I added a solution above.

Collectives™ on Stack Overflow

Get data from table, Using Rows as Columns

4 Answers 4

2 Comments

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related