Posted by Erland Sommarskog on 09/26/56 11:58

(muzili@gmail.com) writes:
> I have two tables, one is about member infomations, the other is the
> catergories
> member_info(id,name,email,phone)
> member_categories(id,category)
>
> how can create a view like this (id, name, category1, category2,
> category3) with high performance?

SELECT m.id, m.name,
MIN (CASE mc.rowno WHEN 1 THEN mc.category END),
MIN (CASE mc.rowno WHEN 2 THEN mc.category END),
MIN (CASE mc.rowno WHEN 3 THEN mc.category END)
FROM member_info m
JOIN (SELECT id, category,
rowno = row_number() OVER (PARTITION BY id ORDER BY category)
FROM member_categories) AS mc OM m.id = mc.id
WHERE mc.rowno <= 3
GROUP BY m.id, m.name

This solution requires SQL 2005. It's difficult do this in one query in
SQL 2000 and get performance with high volumes.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Download the JAVA , .NET and SQL Server interview PDF
• Prev in forum: Re: sql server i/o bottle neck ?
• Thread view: Re: howto use the query results as view column?

[Reply to this message]