July 30, 2008 at 3:44 am
I am working with SQL Server 2005 and I have a simple table that I create using a simple case statement that looks like this:-
RowID Component Operation
1 Construction Leasing 2UG 0031
2 Construction Inst. Sale 2UG 0036
3 Procurement Inst. Sale 2UG 0037
4 Procurement Leasing 2UG 0047
I would like to reorganize the whole set into a cross table to look like this:-
Operation 1 Operation 2 Operation 3 Operation 4
Component 1
Component 2
Component 3
Component 4
Now this is easy using a case or pivot command if the column set is know however, this set is not known and I am trying to build it dynamically.
I have developed a stored procedure to do all the steps as follows:-
CREATE PROCEDURE BUILD_GRID (@project_code nvarchar(20))
AS
--DECLARE VARIABLES
declare @rowCount int;
declare @ColumnAlias nvarchar(255)
declare @rowID int;
--CREATE TEMPORARY TABLE
create table #ComponentOperations
(
RowID int identity (1,1),
Component_Name nvarchar(50),
Operation nvarchar(50)
)
--INSERT RECORDS
insert into #ComponentOperations
(Component_Name, Operation)
select c.component_txt,
case
when po.mod_fin_txt != ' ' then po.mod_fin_txt + ' ' + co.add_no
else ' ' end as Operation
from dbo.wps_project_operation po
inner join dbo.wps_component_operation co
on po.add_no = co.add_no
inner join dbo.wps_project_component pc
on co.component_id = pc.component_id
inner join dbo.wps_component c
on pc.component_id = c.component_id
where po.project_no = @project_code
--BUILD GRID
if exists (select * from #ComponentOperations)
set @rowCount = (select count(*) from #ComponentOperations)
set @rowID = 1
While @rowid <= @rowCount
begin
set @ColumnAlias = (select operation from #ComponentOperations where rowID = @rowID)
exec('select component_name,
case
when Operation != '' '' then 0
else '' '' end as ''' + @ColumnAlias +
'''from #ComponentOperations')
set @rowid = @rowid + 1
end
GO
the behaviour of the procedure is correct but instead of getting one result set containing all the columns I get different 10 different result sets (one for each column) as follows:-
Operation 1
Component 1
Component 2
Component 3
Component 4
Operation 2
Component 1
Component 2
Component 3
Component 4
etc...
Your help is much appreciated!!!
Cheers
Mamoon
July 30, 2008 at 4:05 am
Hi,
Here is a section of Code that will create a dynamic pivot:
I have used a UNION select to insert my data, but you will need to replace this with your main query, but you should get the jist of what I am trying to do:
CREATE TABLE #ComponentOperations
(Component_Name VARCHAR(100),Operation VARCHAR(100))
INSERT INTO #ComponentOperations
(Component_Name,Operation)
SELECT 'Com1','Op1' UNION ALL
SELECT 'Com2','Op2' UNION ALL
SELECT 'Com3','Op3' UNION ALL
SELECT 'Com4','Op4' UNION ALL
SELECT 'Com1','Op5'
DECLARE @ColsList AS VARCHAR(4000)
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Operation + ']'
FROM #ComponentOperations t
EXEC ('SELECT *
FROM
(
SELECT
Component_Name,
Operation
FROM #ComponentOperations
) t
PIVOT (COUNT(Operation) FOR Operation IN (' + @ColsList + ')) PVT')
Let me know if you need any other help.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 30, 2008 at 5:17 am
THANK YOU SO MUCH CHRIS, it works now, really appreciate it!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply