July 25, 2011 at 4:40 pm
I have a report that needs to consolidate information. It needs to read:
empID|name|'trainingNameX' | 'trainingDateForX' | 'trainingNameY' | 'trainingDateForY' | ....etc
1.........John.........IT...................5/16/2011...........Ethics..................5/20/2011
2.........Sally.........IT...................NULL..................Ethics..................6/6/2011
The real 'trainingNameX' , 'trainingNameY' need to be the retrieved from a 'training' table (would really be IT or Ethics in the example)
There is a mapping table that groups the training date with the employee. Note that if the employee does not have a training date associated to the training type then there is no entry in the mapping table.
I want the column names to be dynamic as the users may add multiple training types.
I though about doing all of this in a cursor to alter the table and add a col, but I don't think that is the best approach.
July 25, 2011 at 6:43 pm
Use methods similar to those found in the following article...
http://www.sqlservercentral.com/articles/Crosstab/65048/
Instead of using SUM or COUNT on the DATETME and character based columns, use MAX, instead.
If you'd like a coded answer to you question, please see the first link below in my signature line (which I strongly recommend you read/heed for when you make another post).
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2011 at 8:22 am
Jeff,
I appreciate the pointers, but I am still lost. I am just starting to tread the waters when it comes to working with SQL so I'm a little slow.
Below is some code that sort of displays what I am trying to accomplish in terms of the column names and the dates. However when an employee does not have a date in the mapping table it needs to read 'NULL'. In the snippet, Frank is a great example - he should read NULL all the way across.
--create training table
CREATE TABLE #training(
[trainingID] [int] NOT NULL,
[trainingName] [nvarchar](50) NULL,
CONSTRAINT [PK_training] PRIMARY KEY CLUSTERED
(
[trainingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--create employee table
CREATE TABLE #employee(
[employeeID] [int] NOT NULL,
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[employeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--create mapping table
CREATE TABLE #map_employeeAndTraining(
[mapEmployeeAndTrainingID] [int] NOT NULL,
[employeeID] [int] NULL,
[trainingID] [int] NULL,
[dateOfTraining] [datetime] NULL,
CONSTRAINT [PK_map_employeeAndTraining] PRIMARY KEY CLUSTERED
(
[mapEmployeeAndTrainingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--============END OF CREATING TABLES==========--
--============INSERT DATA==========--
INSERT INTO #training (trainingID, trainingName ) VALUES( 1, 'IT')
INSERT INTO #training (trainingID, trainingName ) VALUES( 2, 'Ethics')
INSERT INTO #training (trainingID, trainingName ) VALUES( 3, 'Corporate')
INSERT INTO #training (trainingID, trainingName ) VALUES( 4, 'CPR')
INSERT INTO #employee (employeeID, firstName, lastName ) VALUES (1, 'John', 'Smith')
INSERT INTO #employee (employeeID, firstName, lastName ) VALUES (2, 'Sally', 'May')
INSERT INTO #employee (employeeID, firstName, lastName ) VALUES (3, 'Frank', 'Jones')
INSERT INTO #map_employeeAndTraining (mapEmployeeAndTrainingID, employeeID, trainingID, dateOfTraining) VALUES(1, 1, 1, '1/1/2011')
INSERT INTO #map_employeeAndTraining (mapEmployeeAndTrainingID, employeeID, trainingID, dateOfTraining) VALUES(2, 1, 2, '1/2/2011')
INSERT INTO #map_employeeAndTraining (mapEmployeeAndTrainingID, employeeID, trainingID, dateOfTraining) VALUES(3, 1, 3, '1/3/2011')
INSERT INTO #map_employeeAndTraining (mapEmployeeAndTrainingID, employeeID, trainingID, dateOfTraining) VALUES(4, 1, 4, '1/4/2011')
INSERT INTO #map_employeeAndTraining (mapEmployeeAndTrainingID, employeeID, trainingID, dateOfTraining) VALUES(5, 2, 1, '2/3/2011')
--SELECT DATA
SELECT e.employeeID, e.firstName, e.lastName, map.dateOfTraining AS [IT], map.dateOfTraining AS [Ethics], map.dateOfTraining AS [Corporate]
FROM #employee AS e
INNER JOIN #map_employeeAndTraining AS map ON e.employeeID = map.employeeID
SELECT * FROM #employee
SELECT * FROM #training
SELECT * FROM #map_employeeAndTraining
--============CLEAN UP==========--
DROP TABLE #employee
DROP TABLE #training
DROP TABLE #map_employeeAndTraining
July 26, 2011 at 4:16 pm
DISCLAIMER: Before I get too much egg on my face, let me say that a) I am cringing as I click "Post Reply" b) it's actually been so long since I've written a cursor that I had two syntax errors from the get-go and c) I anxiously await a non-cursor solution.
CREATE TABLE #result
(employeeID int NOT NULL,
firstName nvarchar(50) NOT NULL,
lastName nvarchar(50) NOT NULL)
INSERT INTO #result
SELECT DISTINCT employeeID,
firstName,
lastName
from #employee
DECLARE @columnName nvarchar(50)
DECLARE @trainingID int
DECLARE @sql varchar(200)
DECLARE training_cursor CURSOR FOR
SELECT trainingID, trainingName
FROM #training
OPEN training_cursor
FETCH NEXT FROM training_cursor INTO @trainingID, @columnName
WHILE @@fetch_status = 0
BEGIN
SELECT @sql = 'alter table #result add [' + @columnName + '_Training] nvarchar(50)'
EXEC(@sql)
SELECT @sql = 'alter table #result add [' + @columnName + '] datetime'
EXEC(@sql)
SELECT @sql = 'UPDATE #result'
SELECT @sql = @sql + ' SET [' + @columnName + '_Training] = ''' + @columnName + ''','
SELECT @sql = @sql + '[' + @columnName + '] = map.dateOfTraining'
SELECT @sql = @sql + ' FROM #result r'
SELECT @sql = @sql + ' INNER JOIN #map_EmployeeAndTraining map on r.employeeID = map.employeeID'
SELECT @sql = @sql + ' where map.trainingID = ' + convert(varchar, @trainingID)
EXEC(@SQL)
FETCH NEXT FROM training_cursor INTO @trainingID, @columnName
END
CLOSE training_cursor
DEALLOCATE training_cursor
SELECT * FROM #result
July 26, 2011 at 5:28 pm
SSC thanks for the post! I had a similar approach, but my code was less efficient so it is helpful 🙂
I too am interested to see a no cursor solution.
July 26, 2011 at 9:39 pm
This is a simple "pivot" problem... except I don't care for using PIVOT because it's slower than classic "Cross Tab" code and I don't care for its structure. 😉
Let's peel one potato at a time. First, does the following code produce the output you'd like to see from the readily consumable test data you were kind enough to provide?
WITH
cteGatherData AS
(
SELECT emp.employeeID, emp.firstName, emp.lastName, trn.trainingName, map.dateOfTraining
FROM #employee emp
LEFT JOIN #map_employeeAndTraining map ON emp.employeeID = map.employeeID
LEFT JOIN #training trn ON map.trainingID = trn.trainingID
)
SELECT employeeID, firstName, lastName,
IT = MAX(CASE WHEN trainingName = 'IT' THEN dateOfTraining ELSE NULL END),
Ethics = MAX(CASE WHEN trainingName = 'Ethics' THEN dateOfTraining ELSE NULL END),
CPR = MAX(CASE WHEN trainingName = 'CPR' THEN dateOfTraining ELSE NULL END),
Corporate = MAX(CASE WHEN trainingName = 'Corporate' THEN dateOfTraining ELSE NULL END)
FROM cteGatherData
GROUP BY employeeID, firstName, lastName
ORDER BY employeeID
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2011 at 11:35 am
It does get me the display I'm looking for given I know all of the training names in advance. The number of columns needs to be dynamic.
If a user adds a new 'trainingType' and starts recording dates, the new training type needs to show up in the report automatically.
July 27, 2011 at 5:02 pm
ng.work (7/27/2011)
It does get me the display I'm looking for given I know all of the training names in advance. The number of columns needs to be dynamic.If a user adds a new 'trainingType' and starts recording dates, the new training type needs to show up in the report automatically.
Understood. Just like I said in the article I posted a link for, you have to get something working first (ie: Peel one potato at a time). I'll be back later tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2011 at 5:59 pm
Here we go... think "Teach someone to fish...". 😉
If we take the working code that I previously posted and mark the sections that are static and those that are dynamic, here's what we come up with... I call it "The Plan"...
------- This is a "Static" section of code that doesn't change
WITH
cteGatherData AS
(
SELECT emp.employeeID, emp.firstName, emp.lastName, trn.trainingName, map.dateOfTraining
FROM #employee emp
LEFT JOIN #map_employeeAndTraining map ON emp.employeeID = map.employeeID
LEFT JOIN #training trn ON map.trainingID = trn.trainingID
)
SELECT employeeID, firstName, lastName,
------- This is the only "Dynamic" section of code there is for this problem
-- Notice that only the literal trainingName changes between these lines of code.
-- Makes life real simple when it comes to dynamic SQL as you'll soon see.
IT = MAX(CASE WHEN trainingName = 'IT' THEN dateOfTraining ELSE NULL END),
Ethics = MAX(CASE WHEN trainingName = 'Ethics' THEN dateOfTraining ELSE NULL END),
CPR = MAX(CASE WHEN trainingName = 'CPR' THEN dateOfTraining ELSE NULL END),
Corporate = MAX(CASE WHEN trainingName = 'Corporate' THEN dateOfTraining ELSE NULL END)
------- This is also a "Static" section in the code that doesn't change
FROM cteGatherData
GROUP BY employeeID, firstName, lastName
ORDER BY employeeID
;
3 sections of code. The 1st and 3rd sections are static. The 2nd section is dynamic and will be formed based on the content of the "trainingName" column. The only thing that changes from row to row in the dynamic section is the "trainingName". With that thought in mind, we'll use what has become the "classic" method for concatenation in SQL Server 2005 and up using XML and STUFF. As always, the details are in the comments in the code. 😉
--===== Declare a variable for each section of the code to come.
DECLARE @SQL1 VARCHAR(8000),
@SQL2 VARCHAR(8000),
@SQL3 VARCHAR(8000)
--===== Populate the 3 variables "according to plan".
------- This first variable is for a "static" section and should be easy to understand.
SELECT @SQL1 =
'
WITH
cteGatherData AS
(
SELECT emp.employeeID, emp.firstName, emp.lastName, trn.trainingName, map.dateOfTraining
FROM #employee emp
LEFT JOIN #map_employeeAndTraining map ON emp.employeeID = map.employeeID
LEFT JOIN #training trn ON map.trainingID = trn.trainingID
)
SELECT employeeID, firstName, lastName,
'
,
------- This is a little more difficult to understand. It uses a "Pseudo-Cursor" to
-- create one line of code for each trainingName in the Training table. I added
-- some stuff like SPACE(8) to make it easy to read if you ever need to print
-- it out. I suggest you visit the following article to see how FOR XML PATH
-- is being used for this.
-- http://www.sqlservercentral.com/articles/comma+separated+list/71700/
@SQL2 = (
SELECT STUFF( --This just gets rid of the leading Comma and LineFeed
(
SELECT ',' + CHAR(10) + SPACE(8) +
+ QUOTENAME(trainingName)
+ ' = MAX(CASE WHEN trainingName = '
+ QUOTENAME(trainingName,'''')
+ ' THEN dateOfTraining ELSE NULL END)'
FROM #training
ORDER BY trainingName
FOR XML PATH(''),TYPE
).value('(./text())[1]','varchar(8000)')
,1,2,'')
),
------- This third variable is also for a "static" section and should be easy to understand.
@SQL3 =
'
FROM cteGatherData
GROUP BY employeeID, firstName, lastName
ORDER BY employeeID
;
'
--===== Execute the Dynamic SQL we just made.
--SELECT @SQL1+@SQL2+@SQL3; --Do this with TEXT results instead of a grid
EXEC (@SQL1+@SQL2+@SQL3);
Let me know how you make out on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply