October 17, 2011 at 2:30 pm
Not sure how to create this report most efficiently in SQL Server 2008 R2.
I have a table Initiatives:
Init_ID INT PK
Init_Name Varchar(25)
The number may change at any time
I have another table InitDependencies
DI_ID INT PK
Init_ID INT FK to Initiatives.Init_ID
DM_ID INT FK to DeviceMaster.DM_ID
A device ID may be related to many Initiatives but Init_ID & DM_ID is a unique combination in InitDependencies
I need to be able to create a table/report that would show
Init_Name_1 Init_Name_2 ..... Init_Name_x
DM_ID_1 X X
DM_ID_2 X
So I'm looking at creating a table/view where the rows of Initiatives become columns and rows of InitDependencies.
This can be done as a triggered stored procedure.
What's the best way of accomplishing this?
Thanks
October 17, 2011 at 2:35 pm
im not sure i entirly get what your asking for but, as i read it sounds like you want to pivot the data, look up pivot tables
***The first step is always the hardest *******
October 17, 2011 at 2:39 pm
The most efficient way to do this is to use the correct tool for the job. That tool is a reporting tool (such as SSRS). T-SQL is most definitely NOT the correct tool for the job even though it is possible to do what you ask in T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2011 at 3:21 pm
Hopefully this explains it more:
Initiative Table
Init_IDInit_Name
1InitiativeA
2InitiativeB
3InitiativeC
InitDependency Table
DI_IDInit_IDDM_ID
11P10
22P10
31Q01
43R22
52R22
Table/Report output
DeviceInitiativeAInitiativeBInitiativeC
P10Yes Yes
Q01Yes
R22 Yes Yes
Seems I need to use a dynamic sql pivot
October 17, 2011 at 3:22 pm
Thanks Drew. I'll have to try that as well
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply