Reputation: 1
TSQL - SQL 2000
I'm struggling with this one. I have a table A which looks like this:
Employee_ID Dependant_FirstName DOB 1 John 12/12/1980 1 Lisa 11/11/1982 2 Mark 06/06/1985 2 Shane 07/07/1982 2 Mike 03/04/1990 3 NULL NULL
and would like to copy these data in Table B like this (knowing that there could only be a maximum of 6 dependants in Table A):
Employee_ID Dependant1_FirstName DOB Dependant2_FirstName DOB Dependant3_FirstName DOB 1 John 12/12/1980 Lisa 11/11/1982 NULL NULL 2 Mark 06/06/1985 Shane 07/07/1982 Mike 03/04/1990 3 NULL NULL NULL NULL NULL NULL
Thanks very much for the help.
Marc
Upvotes: 0
Views: 147
Answers (4)
Reputation: 151
Check this code please, It might work for you.
declare @Emp_Id int
declare @Name int
declare @DOB int
declare @Count int
set @Count=1
DECLARE x_cursor CURSOR FOR
SELECT distinct Employee_ID from tableA
OPEN x_cursor
FETCH NEXT FROM x_cursor
INTO @Emp_Id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE second_cursor CURSOR FOR
SELECT distinct Dependant_FirstName,DOB from tableA
where Employee_ID=@Emp_Id
OPEN second_cursor
FETCH NEXT FROM second_cursor
INTO @Name,@DOB
WHILE @@FETCH_STATUS = 0
BEGIN
if(@Count=1)
begin
insert into tableB (Employee_ID , Dependant1_FirstName,DOB)
values(@Emp_Id,@Name,@DOB)
set @Count=@Count+1
end
else
begin
exec('Update tableB set Dependant'+@count+'_FirstName='+@Name+' ,DOB'+@Count+'='+@DOB+' where Employee_ID='+@Emp_Id)
set @Count=@Count+1
end
FETCH NEXT FROM second_cursor
INTO @Name,@DOB
END
CLOSE second_cursor
DEALLOCATE second_cursor
set @Count=1
FETCH NEXT FROM x_cursor
INTO @Emp_Id
END
CLOSE x_cursor;
DEALLOCATE x_cursor
GO
Upvotes: 1
Reputation: 38238
This is a working example for just your example data, to give an idea of how I'd do it. I'm using a faked-up dependant counter based on date of birth and name. Bear in mind it will break if an employee has twins with the same name, but if they do that, then they deserve all the lifelong data-confusion that they've got in store :)
Also, please consider upgrading that SQL Server. Or moving this kind of pivoting to your reporting tool rather than the database.
CREATE TABLE #employees (employee_id INTEGER, Dependant_FirstName VARCHAR(20), DOB DATETIME)
INSERT INTO #employees VALUES (1, 'John', '12/12/1980')
INSERT INTO #employees VALUES (1, 'Lisa', '11/11/1982')
INSERT INTO #employees VALUES (2, 'Shane', '07/07/1982')
INSERT INTO #employees VALUES (2, 'Mark', '06/06/1985')
INSERT INTO #employees VALUES (2, 'Mike', '03/04/1990')
INSERT INTO #employees VALUES (3, NULL, NULL)
SELECT
employee_id,
MAX(CASE WHEN dep_count = 1 THEN Dependant_FirstName ELSE NULL END) 'Dependant1_FirstName',
MAX(CASE WHEN dep_count = 1 THEN DOB ELSE NULL END) 'Dependant1_DOB',
MAX(CASE WHEN dep_count = 2 THEN Dependant_FirstName ELSE NULL END) 'Dependant2_FirstName',
MAX(CASE WHEN dep_count = 2 THEN DOB ELSE NULL END) 'Dependant2_DOB',
MAX(CASE WHEN dep_count = 3 THEN Dependant_FirstName ELSE NULL END) 'Dependant3_FirstName',
MAX(CASE WHEN dep_count = 3 THEN DOB ELSE NULL END) 'Dependant3_DOB'
FROM
(
SELECT
employee_id,
Dependant_FirstName,
DOB,
(
SELECT
COUNT(*)
FROM
#employees deps
WHERE
#employees.employee_id = deps.employee_id AND
CONVERT(VARCHAR, #employees.DOB, 126) + #employees.Dependant_FirstName <=
CONVERT(VARCHAR, deps.DOB, 126) + deps.Dependant_FirstName
) 'dep_count'
FROM
#employees
) add_dep_count_query
GROUP BY
employee_id
Upvotes: 3
Reputation: 58491
You could
- Create a view
- Calculate a fictuous ranking
- Group to find the maximum ranking for each employee_ID
- return the results.
Note: I have ommitted the DOB column in the examples
Statement
CREATE VIEW dbo.VIEW_Employees_Ranking AS
SELECT Ranking = ISNULL(e6.Employee_ID, 0)
+ ISNULL(e5.Employee_ID, 0)
+ ISNULL(e4.Employee_ID, 0)
+ ISNULL(e3.Employee_ID, 0)
+ ISNULL(e2.Employee_ID, 0)
+ ISNULL(e1.Employee_ID, 0)
, e1.Employee_ID
, Name1 = e1.Dependant_FirstName
, Name2 = e2.Dependant_FirstName
, Name3 = e3.Dependant_FirstName
, Name4 = e4.Dependant_FirstName
, Name5 = e5.Dependant_FirstName
, Name6 = e6.Dependant_FirstName
FROM dbo.Employees e1
LEFT OUTER JOIN dbo.Employees e2 ON e2.Employee_ID = e1.Employee_ID AND e2.DOB > e1.DOB
LEFT OUTER JOIN dbo.Employees e3 ON e3.Employee_ID = e2.Employee_ID AND e3.DOB > e2.DOB
LEFT OUTER JOIN dbo.Employees e4 ON e4.Employee_ID = e3.Employee_ID AND e4.DOB > e3.DOB
LEFT OUTER JOIN dbo.Employees e5 ON e5.Employee_ID = e4.Employee_ID AND e5.DOB > e4.DOB
LEFT OUTER JOIN dbo.Employees e6 ON e6.Employee_ID = e5.Employee_ID AND e6.DOB > e5.DOB
GO
SELECT er.*
FROM dbo.VIEW_Employees_Ranking er
INNER JOIN (
SELECT Ranking = MAX(Ranking)
, Employee_ID
FROM dbo.VIEW_Employees_Ranking
GROUP BY
Employee_ID
) ermax ON ermax.Ranking = er.Ranking AND ermax.Employee_ID = er.Employee_ID
Upvotes: 1
Reputation: 4467
Have a look at this example:
http://ryanfarley.com/blog/archive/2005/02/17/1712.aspx
here he is concatentating the child elements of a parent key into a string which should allow you to write out a flat record.
Upvotes: 0