SQL Server 2008 T-SQL Join problem ver 2

Question

I have some table with data

Category

CategoryID     CategoryName

1              Home
2              Contact
3              About

Position

PositionID     PositionName

1              Main menu
2              Left menu
3              Right menu

...(new record can be added later)

CategoryPosition

CPID   CID    PID    COrder

1      1      1      1 
2      1      2      2
3      1      3      3
4      2      1      4
5      2      3      5

How can i make a table like that

CID    CName     MainMenu   LeftMenu   RightMenu

1      Home      1          2          3
2      Contact   4          0          5
3      About     0          0          0

And if a new Category or position record is added later, the querry should work automatically, e.g:

CID    CName     MainMenu   LeftMenu   RightMenu   BottomMenu

1      Home      1          2          3           0
2      Contact   4          0          5           0
3      About     0          0          0           0
4      News      0          0          0           0

Please help me, thanks you.

Answer 1

Since PIVOT requires a static list of columns, I think a dynamic-sql-based approach is really all that you can do: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Answer 2

I guess you need to select using PIVOT. By default, pivots only select a static list of columns. There are some solutions on the net dealing with dynamic column pivots, such as here and here.

Answer 3

The following dynamic query seems to work:

declare @columnlist nvarchar(4000)
select @columnlist = IsNull(@columnlist + ', ', '') + '[' + PositionName + ']'
from #Position

declare @query nvarchar(4000)
select @query = '
    select *
    from (
     select CategoryId, CategoryName, PositionName, 
                IsNull(COrder,0) as COrder
     from #Position p
     cross join #Category c
     left join #CategoryPosition cp 
                on cp.pid = p.PositionId 
                and cp.cid = c.CategoryId
    ) pv
    PIVOT (max(COrder) FOR PositionName in (' + @columnlist + ')) as Y
    ORDER BY CategoryId, CategoryName
'

exec sp_executesql @query

Some clarification:

• The @columnlist contains the dymamic field list, built from the Positions table
• The cross join creates a list of all categories and all positions
• The left join seeks the corresponding COrder
• max() selects the highest COrder per category+position, if there is more than one
• PIVOT() turns the various PositionNames into separate columns

P.S. My table names begin with #, because I created them as temporary tables. Remove the # to refer to a permanent table.

P.S.2. If anyone wants to try his hands at this, here is a script to create the tables in this question:

set nocount on 

if object_id('tempdb..#Category') is not null drop table #Category
create table #Category (
    CategoryId int identity,
    CategoryName varchar(50)
)

insert into #Category (CategoryName) values ('Home')
insert into #Category (CategoryName) values ('Contact')
insert into #Category (CategoryName) values ('About')
--insert into #Category (CategoryName) values ('News')

if object_id('tempdb..#Position') is not null drop table #Position
create table #Position (
    PositionID int identity,
    PositionName varchar(50)
)

insert into #Position (PositionName) values ('Main menu')
insert into #Position (PositionName) values ('Left menu')
insert into #Position (PositionName) values ('Right menu')
--insert into #Position (PositionName) values ('Bottom menu')

if object_id('tempdb..#CategoryPosition') is not null 
    drop table #CategoryPosition
create table #CategoryPosition (
    CPID int identity,
    CID int,
    PID int,
    COrder int
)

insert into #CategoryPosition (CID, PID, COrder) values (1,1,1)
insert into #CategoryPosition (CID, PID, COrder) values (1,2,2)
insert into #CategoryPosition (CID, PID, COrder) values (1,3,3)
insert into #CategoryPosition (CID, PID, COrder) values (2,1,4)
insert into #CategoryPosition (CID, PID, COrder) values (2,3,5)

Answer 4

As mentioned by several posters, dynamic SQL using the PIVOT command is the way to go. I wrote a stored proc named pivot_query.sql awhile back that has been very handy for this purpose. It works like this:

-- Define a query of the raw data and put it in a variable (no pre-grouping required)
declare @myQuery varchar(MAX);

set @myQuery = '
select
   cp.cid,
   c.CategoryName,
   p.PositionName,
   cp.COrder
from
   CategoryPosition cp

   JOIN Category c
      on (c.CategoryId = cp.cid)

   JOIN Position p
      on (p.PositionId = cp.pid)';

-- Call the proc, passing the query, row fields, pivot column and summary function
exec dbo.pivot_query @myQuery, 'CategoryName', 'PositionName', 'max(COrder) COrder'

The full syntax of the pivot_query call is:

pivot_query '<query>', '<field list for each row>', '<pivot column>', '<aggregate expression list>', '[<results table>]', '[<show query>]'

it is explained more in the comments at the top of the source code.

A couple of advantages of this proc are that you can specify multiple summary functions like max(COrder),min(COrder) etc. and it has the option to store the output in a table in case you want to join the summary data up with other information.

Ron

Answer 5

My suggestion would be to return your data as a simple join and let the front end sort it out. There are some things for which SQL is excellent, but this particular problem seems like something that the front end should be doing. Of course, I can't know that without knowing your full situation, but that's my hunch.