Hi All,

I have two tables as category and product

Category table as

categoryid categoryname

1 Sample

2 new


and product table as

prodid product category id
1 myprod 1
2 test prd 1
3 newprod 2
4 crprod 2


I just wanted to display the records like

Sample New
--------------------------
myprod newprod
test prod crprod


Please suggest me a solution

Thanks in Advance.

SeNa wrote:
I was going to suggest a pivot but you don't have a grouping column to
use as the row "header" (i.e., a column that relates myprod to newprod
and test prd to crprod). Assuming SQL 2005 or later, this will work:

WITH
new AS (
select ROW_NUMBER() OVER (ORDER BY prodid) AS RowNum
,product FROM product WHERE categoryid=2
), sample AS (
select ROW_NUMBER() OVER (ORDER BY prodid) AS RowNum
,product FROM product WHERE categoryid=1)
SELECT sample.product Sample,new.product New
FROM new FULL OUTER JOIN sample
ON new.rownum=sample.rownum

--
HTH,
Bob Barrows

On Nov 10, 8:41*pm, "Bob Barrows" <reb01... (AT) NOyahoo (DOT) SPAMcom> wrote:
Hi thanks for your reply.

I am using sql 2005

and the query should be dyanamic where i wanted to avoid modifications
in future.

whenever i add a category and product should reflect in data pull

Thanks

SeNa wrote:
string variable that contains the sql statement needed and using EXECUTE
or sp_executesql to execute it. I don't have the time right now to throw
a script together for you but here's a link that might prove helpful:
http://www.sommarskog.se/dynamic_sql.html

The idea is to use a cursor to loop through the Category table, using
each category id and name to generate each WITH statement as well as the
final select statement

--
HTH,
Bob Barrows