|
|
Posted by Erland Sommarskog on 01/31/08 22:40
Chris H (chollstein@broadreachpartnersinc.com) writes:
> The current joins look something like this:
> LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier1 ON
> qualifier1.company_id = oncd_company.company_id AND
> (qualifier1.company_qualifier_question_id = 'Q01' OR
> qualifier1.company_qualifier_question_response_id IS NULL)
> LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses1
> ON qualifier_responses1.quest_resp_id =
> qualifier1.company_qualifier_response_id
> LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier2 ON
> qualifier2.company_id = oncd_company.company_id AND
> (qualifier2.company_qualifier_question_id = 'Q02' OR
> qualifier2.company_qualifier_question_response_id IS NULL)
> LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses2
> ON qualifier_responses2.quest_resp_id =
> qualifier2.company_qualifier_response_id
> LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier3 ON
> qualifier3.company_id = oncd_company.company_id AND
> (qualifier3.company_qualifier_question_id = 'Q03' OR
> qualifier3.company_qualifier_question_response_id IS NULL)
> LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses3
> ON qualifier_responses3.quest_resp_id =
> qualifier3.company_qualifier_response_id .... this goes on to the
> last question.
Rather than doing a bunch of left-joins, you could pivot your
table as demonstrated by this example:
SELECT OrderID,
Emp1 = MIN(CASE EmployeeID WHEN 1 THEN 'X' END),
Emp2 = MIN(CASE EmployeeID WHEN 2 THEN 'X' END),
Emp3 = MIN(CASE EmployeeID WHEN 3 THEN 'X' END),
Emp4 = MIN(CASE EmployeeID WHEN 4 THEN 'X' END),
Emp5 = MIN(CASE EmployeeID WHEN 5 THEN 'X' END),
Emp6 = MIN(CASE EmployeeID WHEN 6 THEN 'X' END),
Emp7 = MIN(CASE EmployeeID WHEN 7 THEN 'X' END),
Emp8 = MIN(CASE EmployeeID WHEN 8 THEN 'X' END),
Emp9 = MIN(CASE EmployeeID WHEN 9 THEN 'X' END)
FROM Orders
WHERE OrderDate BETWEEN '19970101' AND '19970131'
GROUP BY OrderID
ORDER BY OrderID
In SQL 2005, you can also use the PIVOT keyword, but I don't use it
myself.
As for generating all these views, you may be better doing this from
a traditional language.
--
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
Navigation:
[Reply to this message]
|