January 4, 2012 at 10:24 pm
Hi,
I have two tables, one for Employees details and another for employee's family member details, somethings as follows:
Table-1: employee_master
emp_id emp_namedob salary join_date
1 aaaa01-3-1976 12000 01-03-2009
2 bbbb09-09-1980 8600 10-08-2009
3 cccc13-02-1977 9400 12-04-2008
Table-2: family_members
emp_idmember_idmember_nameage
1 1aa1 34
1 2aa2 12
1 3aa3 8
2 1bb1 29
2 2bb2 10
3 1cc1 58
3 2cc2 57
3 3cc3 30
3 4cc4 10
Now I want to merge these two tables to get the report as follows:
emp_id, emp_name, dob, salary, join_date, member_1_id, member_1_name, member_1_age, member_2_id, member_2_name, member_2_age, member_3_id, member_3_name, member_3_age, etc
How can I convert multiple rows into multiple columns in one row for family-member talbe to show result as above?
January 5, 2012 at 2:15 am
This article contains an explanation of the method used[/url].
BEGIN TRAN
--Sample data
CREATE TABLE employee_master (emp_id INT PRIMARY KEY, emp_name VARCHAR(4), dob DATETIME,
salary INT, join_date DATETIME)
INSERT INTO employee_master
SELECT 1, 'aaaa', '1976-03-01', 12000, '2009-03-01'
UNION ALL SELECT 2, 'bbbb', '1980-09-09', 8600, '2009-08-10'
UNION ALL SELECT 3, 'cccc', '1977-02-13', 9400, '2008-04-12'
--Sample data
CREATE TABLE family_members (emp_id INT, member_id INT, member_name VARCHAR(3), age INT)
INSERT INTO family_members
SELECT 1, 1, 'aa1', 34
UNION ALL SELECT 1, 2, 'aa2', 12
UNION ALL SELECT 1, 3, 'aa3', 8
UNION ALL SELECT 2, 1, 'bb1', 29
UNION ALL SELECT 2, 2, 'bb2', 10
UNION ALL SELECT 3, 1, 'cc1', 58
UNION ALL SELECT 3, 2, 'cc2', 57
UNION ALL SELECT 3, 3, 'cc3', 30
UNION ALL SELECT 3, 4, 'cc4', 10
--Actual query
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + 'MAX(CASE WHEN member_id = '+CONVERT(NVARCHAR(6),member_id)+' THEN member_id ELSE NULL END) AS member_'
+CONVERT(NVARCHAR(6),member_id)+'_id,'+ CHAR(10)
FROM (SELECT DISTINCT b.member_id
FROM employee_master a
INNER JOIN family_members b ON a.emp_id = b.emp_id) crossTab
SELECT @SQL = 'SELECT emp_id, emp_name, dob, salary, join_date, ' + CHAR(10) + STUFF(@SQL,LEN(@SQL)-1,1,'') +
'FROM (SELECT a.emp_id, a.emp_name, a.dob, a.salary, a.join_date, ' +
'b.member_id, b.member_name, b.age FROM employee_master a INNER JOIN family_members b ON a.emp_id = b.emp_id) crossTab ' + CHAR(10) +
'GROUP BY emp_id, emp_name, dob, salary, join_date'
EXEC (@SQL)
ROLLBACK
Produces: -
emp_id emp_name dob salary join_date member_1_id member_2_id member_3_id member_4_id
----------- -------- ----------------------- ----------- ----------------------- ----------- ----------- ----------- -----------
1 aaaa 1976-03-01 00:00:00.000 12000 2009-03-01 00:00:00.000 1 2 3 NULL
2 bbbb 1980-09-09 00:00:00.000 8600 2009-08-10 00:00:00.000 1 2 NULL NULL
3 cccc 1977-02-13 00:00:00.000 9400 2008-04-12 00:00:00.000 1 2 3 4
January 5, 2012 at 3:49 am
Dear Cadavre,
Thank you for reply.
I tried this way to get member_id, member_name, age also in the same row, but failed.
SELECT @SQL = COALESCE(@SQL,'') + 'MAX(CASE WHEN member_id = '+CONVERT(NVARCHAR(6),member_id)+' THEN member_id ELSE null END) AS member_'
+CONVERT(NVARCHAR(6),member_id)+'_id,' + 'member_name,'+ + 'age,'+ CHAR(10)
FROM (SELECT DISTINCT b.member_id, b.member_name FROM employee_master a
INNER JOIN family_members b ON a.emp_id = b.emp_id) crossTab
SELECT @SQL = 'SELECT emp_id, emp_name, dob, salary, join_date, ' + CHAR(10) + STUFF(@SQL,LEN(@SQL)-1,1,'') +
'FROM (SELECT a.emp_id, a.emp_name, a.dob, a.salary, a.join_date, ' +
'b.member_id, b.member_name, b.age FROM employee_master a INNER JOIN family_members b ON a.emp_id = b.emp_id) crossTab ' + CHAR(10) +
'GROUP BY emp_id, emp_name, dob, salary, join_date,member_name,age'
I need to get all the details of one employee in one row only. Can you please guide me on this?
January 5, 2012 at 4:20 am
rajeev_vandakar-782919 (1/5/2012)
Dear Cadavre,Thank you for reply.
I tried this way to get member_id, member_name, age also in the same row, but failed.
I need to get all the details of one employee in one row only. Can you please guide me on this?
Did you read the article I pointed you at? It explains how the method works which would have helped you to adapt the solution I posted.
This is how you would add the other bits.
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + 'MAX(CASE WHEN member_id = '+CONVERT(NVARCHAR(6),member_id)+' THEN member_id ELSE NULL END) AS member_'
+CONVERT(NVARCHAR(6),member_id)+'_id,'+ CHAR(10) +
'MAX(CASE WHEN member_id = '+CONVERT(NVARCHAR(6),member_id)+' THEN member_name ELSE NULL END) AS member_'
+CONVERT(NVARCHAR(6),member_id)+'_name,'+ CHAR(10) +
'MAX(CASE WHEN member_id = '+CONVERT(NVARCHAR(6),member_id)+' THEN age ELSE NULL END) AS member_'
+CONVERT(NVARCHAR(6),member_id)+'_age,'+ CHAR(10)
FROM (SELECT DISTINCT b.member_id
FROM employee_master a
INNER JOIN family_members b ON a.emp_id = b.emp_id) crossTab
SELECT @SQL = 'SELECT emp_id, emp_name, dob, salary, join_date, ' + CHAR(10) + STUFF(@SQL,LEN(@SQL)-1,1,'') +
'FROM (SELECT a.emp_id, a.emp_name, a.dob, a.salary, a.join_date, ' +
'b.member_id, b.member_name, b.age FROM employee_master a INNER JOIN family_members b ON a.emp_id = b.emp_id) crossTab ' + CHAR(10) +
'GROUP BY emp_id, emp_name, dob, salary, join_date'
EXEC (@SQL)
January 6, 2012 at 2:16 am
Sorry I didn't noticed that! I will read it first now. Thank you again.
January 9, 2012 at 7:21 am
Another possible way to accomplish what you need is to use subqueries to get the parts and left join them to the main table:
SELECT e.emp_id, e.emp_name, e.join_date,
m1.member_id AS member_1_id, m1.member_name AS member_1_name, m1.age AS member_1_age,
m2.member_id AS member_2_id, m2.member_name AS member_2_name, m2.age AS member_2_age,
m3.member_id AS member_3_id, m3.member_name AS member_3_name, m3.age AS member_3_age
FROM employee_master e
LEFT JOIN (SELECT * FROM family_members WHERE member_id = 1) m1 ON m1.emp_id = e.emp_id
LEFT JOIN (SELECT * FROM family_members WHERE member_id = 2) m2 ON m2.emp_id = e.emp_id
LEFT JOIN (SELECT * FROM family_members WHERE member_id = 3) m3 ON m3.emp_id = e.emp_id
This method would work if you need a consistent number of fields in your return set because it is not dynamic. Duplicate the left join as many times as needed.
January 9, 2012 at 7:30 am
fahey.jonathan (1/9/2012)
Another possible way to accomplish what you need is to use subqueries to get the parts and left join them to the main table:
SELECT e.emp_id, e.emp_name, e.join_date,
m1.member_id AS member_1_id, m1.member_name AS member_1_name, m1.age AS member_1_age,
m2.member_id AS member_2_id, m2.member_name AS member_2_name, m2.age AS member_2_age,
m3.member_id AS member_3_id, m3.member_name AS member_3_name, m3.age AS member_3_age
FROM employee_master e
LEFT JOIN (SELECT * FROM family_members WHERE member_id = 1) m1 ON m1.emp_id = e.emp_id
LEFT JOIN (SELECT * FROM family_members WHERE member_id = 2) m2 ON m2.emp_id = e.emp_id
LEFT JOIN (SELECT * FROM family_members WHERE member_id = 3) m3 ON m3.emp_id = e.emp_id
This method would work if you need a consistent number of fields in your return set because it is not dynamic. Duplicate the left join as many times as needed.
The whole point of the dynamic answer is that you don't have to know how many items there are for each part (as explained in the article I linked in my first post).
If you know how many there are, the best bet is to script it like this (the same as the query built dynamically above): -
SELECT emp_id, emp_name, dob, salary, join_date,
MAX(CASE WHEN member_id = 1 THEN member_id ELSE NULL END) AS member_1_id,
MAX(CASE WHEN member_id = 1 THEN member_name ELSE NULL END) AS member_1_name,
MAX(CASE WHEN member_id = 1 THEN age ELSE NULL END) AS member_1_age,
MAX(CASE WHEN member_id = 2 THEN member_id ELSE NULL END) AS member_2_id,
MAX(CASE WHEN member_id = 2 THEN member_name ELSE NULL END) AS member_2_name,
MAX(CASE WHEN member_id = 2 THEN age ELSE NULL END) AS member_2_age,
MAX(CASE WHEN member_id = 3 THEN member_id ELSE NULL END) AS member_3_id,
MAX(CASE WHEN member_id = 3 THEN member_name ELSE NULL END) AS member_3_name,
MAX(CASE WHEN member_id = 3 THEN age ELSE NULL END) AS member_3_age,
MAX(CASE WHEN member_id = 4 THEN member_id ELSE NULL END) AS member_4_id,
MAX(CASE WHEN member_id = 4 THEN member_name ELSE NULL END) AS member_4_name,
MAX(CASE WHEN member_id = 4 THEN age ELSE NULL END) AS member_4_age
FROM (SELECT a.emp_id, a.emp_name, a.dob, a.salary, a.join_date, b.member_id, b.member_name, b.age
FROM employee_master a
INNER JOIN family_members b ON a.emp_id = b.emp_id) crossTab
GROUP BY emp_id, emp_name, dob, salary, join_date
This has the benefit of not hitting the tables as often as your version, which we can see from the IO
Your script IOTable 'family_members'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'employee_master'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
My script IOTable 'family_members'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'employee_master'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply