Join Dynamic Rod (version 2)
I have several tables with data:
Category
CategoryID CategoryName
1 Home
2 Contact
3 About
Position
PositionID PositionName
1 Main menu
2 Left menu
3 Right menu
... (new line 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 to create a table like this:
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 row is added later, the query should automatically reflect the change, for example:
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
a source to share
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 clarifications:
- @Column list contains dynamic fields list built from Positions table
- Cross join creates a list of all categories and all positions
- The left join looks for a matching COrder
- max () selects the highest COrder position for the + category if there is more than one
- PIVOT () turns different PositionNames into separate columns
PS My table names start with C # because I created them as temporary tables. Remove the # to reference a persistent table.
PS2. If anyone wants to try it out, here is a script to create 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)
a source to share
Since PIVOT requires a static list of columns, I believe a dynamic-sql based approach is all you can do: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/ 14 / 37265.aspx
a source to share
As mentioned by several posters, dynamic SQL with PIVOT command is the way to go. I wrote a stored procedure called pivot_query.sql a while ago which was 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 complete syntax for calling pivot_query is:
pivot_query '<query>', '<field list for each row>', '<pivot column>', '<aggregate expression list>', '[<results table>]', '[<show query>]'
this is explained more in the comments at the top of the source code .
A couple of advantages of this process is that you can specify multiple pivot functions like max (COrder), min (COrder), etc., and it has the option to store the output in a table if you want to join the pivot data with other information.
a source to share
My suggestion was to return your data as a simple connection and let the interface figure out. There are some things that SQL is great for, but this particular problem seems like something the front end should be doing. Of course, I cannot know that without knowing about your complete situation, but this is my guess.
a source to share