October 7, 2014 at 6:10 am
CREATE TABLE #tblProjects
(
ProjectID int,
Project varchar(50)
)
GO
INSERT INTO #tblProjects(ProjectID, Project)
SELECT 1, 'Project A' UNION ALL
SELECT 2, 'Project B'
--SELECT * FROM #tblProjects
CREATE TABLE #tblProjectSteps
(
ProjectStepID int,
ProjectID int,
Step varchar(50),
StepDate smalldatetime,
ReportOn bit
)
INSERT INTO #tblProjectSteps(ProjectStepID, ProjectID, Step, StepDate, ReportOn)
SELECT 1, 1, 'Step A1', '01 June 2014', 0 UNION ALL
SELECT 2, 1, 'Step A2', '04 June 2014', 1 UNION ALL
SELECT 3, 1, 'Step A3', '06 June 2014', 1 UNION ALL
SELECT 4, 2, 'Step B1', '02 June 2014', 1 UNION ALL
SELECT 5, 2, 'Step B2', '09 June 2014', 0 UNION ALL
SELECT 6, 2, 'Step B3', '12 June 2014', 1
SELECT * FROM #tblProjectSteps
DROP TABLE #tblProjects
DROP TABLE #tblProjectSteps
GO
I have some projects and each project has a number of steps. Some of the steps I need to return in a select statement for reporting purposes. But I need the Step to be the name of the column and the StepDate to be the value
I need to get the data returned like this for Project A (returning Step A2 and A3 because they are flagged as ReportOn)
ProjectID---ProjectName------Step A2------------Step A3
1------------Project A ----------04 June 2014----06 June 2014
and like this for Project B (returning Step B1 and B3 because they are flagged as ReportOn)
ProjectID---ProjectName------Step B1------------Step B3
1------------Project A ----------02 June 2014-----12 June 2014
Is this possible? I can create a cursor of all the Projects (that have the same ReportOn fields flagged) and loop through this and retrieve the Step and StepDate for the fields flagged as ReportOn) but I can't see how to get that into some sort of temporary table that would allow me to access the data.
October 7, 2014 at 7:33 am
You definitely do not want a cursor here. It sounds like you need to look at cross tabs. If the number of steps is known you can use a static cross tab. However, as I suspect is the case here, you don't know in advance how many steps you will need to use a dynamic cross tab. Check out the articles in my signature for cross tabs. Start with the static cross tab to understand the concept and then move on the dynamic version.
Feel free to post back if you run into any issues and we can help you over the hurdle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply