Combining records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining records

Author

Topic

drunkhammer
Starting Member

5 Posts

Posted - 2010-08-26 : 12:25:12

I'm having difficulty creating a query to combine records. I have a table which contains 1 to 2 records for a specific individual. If an individual has 2 records the records will have different values in 2 fields. I'm attempting to combine these records and insert the data in to a new table. The new table has 4 fields to encapsulate the values of the 2 sets of 2 fields. Below is a basic example of the tables

Initial table
Fields: Id, PersonId, T_Code, T_Name

Sample data:
1, 1234, 45, Milesio
2, 1234, 55, Aegyptiam
3, 3334, 72, Latia

New table
Fields: Id, PersonId, T_Code_1, T_Name_1, T_Code_2, T_Name_2

Resulting data after insert:
1, 1234, 45, Milesio, 55, Aegyptiam
2, 3334, 72, Latia, NULL, NULL

Any assistance is appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-26 : 14:42:14

Here is a query that'll get you the results you want. If you need help with inserting into a new table let us know.

-- Set up Sample Data
DECLARE @Table TABLE (Id INT, PersonId INT, T_Code INT, T_Name VARCHAR(50))
INSERT @Table
SELECT 1, 1234, 45, 'Milesio'
UNION ALL SELECT 2, 1234, 55, 'Aegyptiam'
UNION ALL SELECT 3, 3334, 72, 'Latia'


-- Create CTE
;WITH MyTable
AS
(
SELECT 
	*,
	ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY ID) AS RowNum
FROM 
	@Table
)

-- Return Results
SELECT 
	--A.ID, 
	A.PersonId, 
	A.T_Code AS T_Code_1, 
	A.T_Code AS T_Name_1, 
	B.T_Code AS T_Code_2, 
	B.T_Code AS T_Name_2
FROM 
	MyTable AS A
LEFT OUTER JOIN
	MyTable AS B
	ON A.PersonID = B.PersonID
	AND A.RowNum <> B.RowNum
WHERE
	A.RowNum = 1
	AND
	(
		B.RowNum = 2
		OR B.RowNum IS NULL
	)
ORDER BY 
	A.PersonID