CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ByulTaeng
ByulTaeng

Reputation: 1269

Join with dynamic pivot

Followed up by a new question, that contains the question as text, not as an image like this one:

Join with dynamic pivot (version 2)

(This question is an image. Right click on "I have some table with value". :)

alt text http://img36.imageshack.us/img36/4853/77517349.gif

The query should automaticly work if a new position record is added. Thanks you

Upvotes: 0

Views: 719

Answers (3)

Jeff
Jeff

Reputation: 5963

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

Upvotes: 0

Tomalak
Tomalak

Reputation: 338158

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

Upvotes: 2

jvanderh
jvanderh

Reputation: 2955

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.

Upvotes: 1

Related Questions