SQL Server 2008 T-SQL Join problem

Answer 1

Messy, but it works

select  c.categoryid,c.categoryname
,COALESCE((select top 1 categoryorder from categoryposition where categoryid=c.categoryid and positionid=1),0) as [top]
,COALESCE((select top 1 categoryorder from categoryposition where categoryid=c.categoryid and positionid=2),0) as [left]
,COALESCE((select top 1 categoryorder from categoryposition where categoryid=c.categoryid and positionid=3),0) as [right]   
 from categoryposition cp,category c
where cp.categoryid=c.categoryid
group by c.categoryid,c.categoryname
order by 1

Two things to keep in mind. If you can ensure that there is at most one position for each categoryposition then you can remove the top 1, but the subquery must return 1 row o nothing for it to work.

Answer 2

SELECT
  c.CategoryId AS CID,
  c.CategoryName,
  ISNULL(t.CategoryOrder, 0) AS [Top],
  ISNULL(l.CategoryOrder, 0) AS [Left],
  ISNULL(r.CategoryOrder, 0) AS [Right]
FROM
  Category c
  LEFT JOIN CategoryPosition t ON t.CategoryId = c.CategoryId 
                                  AND t.PositionId = 1
  LEFT JOIN CategoryPosition l ON l.CategoryId = c.CategoryId 
                                  AND l.PositionId = 2
  LEFT JOIN CategoryPosition r ON r.CategoryId = c.CategoryId 
                                  AND r.PositionId = 3

Answer 3

Since you want the cross tab query to be dynamic based on the contents of Position table then I recommend that you dynamicly generate the SQL at runtime.

-- Start with Query frame
DECLARE @Query NVARCHAR(4000)
SET @Query = '
Select
        Category.CategoryID,
        Category.CategoryName
        <DYNAMICQUERY>
    From CategoryPosition
    Inner Join Category ON Category.CategoryID = CategoryPosition.CategoryID
    Group By Category.CategoryID, Category.CategoryName';

SELECT @Query;


-- Build the dynamic part of query
DECLARE @DynamicQuery VARCHAR(1024)
DECLARE @PositionCol VARCHAR(256)
DECLARE dynamic_sql CURSOR FOR
SELECT ',MAX(CASE WHEN CategoryPosition.PositionID = '+CAST(PositionID AS varchar(10)) +' THEN CategoryPosition.CategoryOrder ELSE 0 END) AS ['+PositionName+']' 
From Position

OPEN dynamic_sql

FETCH NEXT FROM dynamic_sql
INTO @PositionCol

SELECT @DynamicQuery = @PositionCol;

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM dynamic_sql
    INTO @PositionCol

    SELECT @DynamicQuery = @DynamicQuery+@PositionCol;
END

CLOSE dynamic_sql
DEALLOCATE dynamic_sql

SELECT @Query = REPLACE(@Query, '<DYNAMICQUERY>', @DynamicQuery)

-- Execute the Query
EXECUTE sp_executesql @Query