If the number of possible values of "Fruit" are fixed and known, you can write this query without using a pivot function. However, this solution becomes untenable if the values of "Fruit" are unknown (at query-writing time) or if there are more than a few values of "Fruit".

The SQL from venkat M will work for the 2 values you provide ('apple','orange') and can be extended for others. But you will see how quickly this becomes clumsy.

If your dbms doesn't support DECODE() function, you can use CASE to the same effect:

Could you not build the entire query dynamically in an SP in this way the column names can be variables while building the eventual 'hard coded' SQL string that gets executed? Not efficient and I suspect sort of insecure but gets the task done.

This is the logic I want to be implement, but am doubtful if am asking too much of programming using SQL, not SP and not using PIVOT. I want to know if it is possible. In the above, we can have num_of_values derived from another subquery.

@Rudra & @Gopinath: Thank you for the query: It still gives the output as rows. I want them as columns.

@Brett, @Shibu, @Bryan & @John: I saw solutions for doing them using PIVOT and SP. In SP, I think it can be made all dynamic without hardcoding column names. In PIVOT, I am still not sure. I have not used PIVOT before, but when I referred articles online, I saw many examples it wanted the column names given. Is it possible in PIVOT to do it without giving column names ?

Hi Jerome,
Thanks ! I was working on a problem which requires this kind of an output. I was trying different options to see if its is possible. The requirement is only to have this output. Though I got the output I wanted with a more customized way, I wanted to know if it is generally possible to have that kind of output.

Thanks Brett! Will refer.

Thanks Sheila, I referred those links, but since it used stored procedure, it did not fit my requirements.

I don't think anyone posted a solution that produced a csv list of fruit names and counts. That's not what you specified, but the method has been explained with examples in response to a number of other threads. I mention this because that technique can be modified to produce an HTML string containing a table with fruit names in the first row and corresponding counts in the second. The downside is that you have to union and concatenate the appropriate tags, but if HTML output will work for you, it addresses the problem of variable number of columns nicely.

Hi Jerome and Steve!
Thanks for all the efforts. The end product is just a query and a result in this particular case. It was kind of a programming skill test in SQL where I had to do this. It is interesting that it can be done in HTML this way, I can look into that. But for now, it is just getting the results and displaying it. Shall also check other threads for related solutions.