Flatten data (SQL)
-
Hi, How do I write the SQL code to select data from these tables: tblPerson ID DOB 1 01/01/2010 2 01/02/2011 3 10/01/2009 tblPersonName ID FName MName LName NameType 1 James D Doe L 1 Jim (null) Doe C 2 Martha (null) Stu L 3 William H Jefferson L 3 Bill (null) Jefferson to look like this (flattened)? ID FName_Legal MName_Legal LName_Legal FName_Common MName_Common LName_Common 1 James D Doe Jim (null) Doe 2 Martha (null) Stu (null) (null) (null) 3 William H Jefferson Bill (null) Jefferson Thanks
-
Hi, How do I write the SQL code to select data from these tables: tblPerson ID DOB 1 01/01/2010 2 01/02/2011 3 10/01/2009 tblPersonName ID FName MName LName NameType 1 James D Doe L 1 Jim (null) Doe C 2 Martha (null) Stu L 3 William H Jefferson L 3 Bill (null) Jefferson to look like this (flattened)? ID FName_Legal MName_Legal LName_Legal FName_Common MName_Common LName_Common 1 James D Doe Jim (null) Doe 2 Martha (null) Stu (null) (null) (null) 3 William H Jefferson Bill (null) Jefferson Thanks
The column headers seem like you want to select the person with the same first, middle OR last name but your results seem like you would select only based on the same last name. However, basically you would use self join. If the last name is used to match rows, it could be something like:
SELECT a.*, b.*
FROM tblPersonName a LEF OUTER JOIN tblPersonName b
ON a.lname = b.lnameThis query has problems though (depending on your specs). If you have three persons with the same last name you get those on multiple rows (each pair). Also each pair will be listed twice (both ways), but I'm not able to say if that's what you want. Also the ID field seems quite suspicious. Do you really have several rows with the same id? If not, that could be used to eliminate the pairs from being listed twice (again if that's what you want).
The need to optimize rises from a bad design.My articles[^]
-
The column headers seem like you want to select the person with the same first, middle OR last name but your results seem like you would select only based on the same last name. However, basically you would use self join. If the last name is used to match rows, it could be something like:
SELECT a.*, b.*
FROM tblPersonName a LEF OUTER JOIN tblPersonName b
ON a.lname = b.lnameThis query has problems though (depending on your specs). If you have three persons with the same last name you get those on multiple rows (each pair). Also each pair will be listed twice (both ways), but I'm not able to say if that's what you want. Also the ID field seems quite suspicious. Do you really have several rows with the same id? If not, that could be used to eliminate the pairs from being listed twice (again if that's what you want).
The need to optimize rises from a bad design.My articles[^]
Hi Mika, Sorry my example wasn't clear.. ID is the PK for tblPerson and a FK in tblPersonName. The join will be done using the ID field. tblPersonName contains a table of all the different names a person has e.g. Legal name, common name etc What I want to do is to be able to list all the different names of a person in one row as opposed to multiple rows. Thanks
-
Hi Mika, Sorry my example wasn't clear.. ID is the PK for tblPerson and a FK in tblPersonName. The join will be done using the ID field. tblPersonName contains a table of all the different names a person has e.g. Legal name, common name etc What I want to do is to be able to list all the different names of a person in one row as opposed to multiple rows. Thanks
Ok, no problem. Basically you could use Pivot[^]. The problem which you seem to encounter is that you'll pivot several columns so I suggest that you also have a look at this excellent article: Pivot two or more columns in SQL Server 2005[^]
The need to optimize rises from a bad design.My articles[^]
-
Ok, no problem. Basically you could use Pivot[^]. The problem which you seem to encounter is that you'll pivot several columns so I suggest that you also have a look at this excellent article: Pivot two or more columns in SQL Server 2005[^]
The need to optimize rises from a bad design.My articles[^]
-
Hi, How do I write the SQL code to select data from these tables: tblPerson ID DOB 1 01/01/2010 2 01/02/2011 3 10/01/2009 tblPersonName ID FName MName LName NameType 1 James D Doe L 1 Jim (null) Doe C 2 Martha (null) Stu L 3 William H Jefferson L 3 Bill (null) Jefferson to look like this (flattened)? ID FName_Legal MName_Legal LName_Legal FName_Common MName_Common LName_Common 1 James D Doe Jim (null) Doe 2 Martha (null) Stu (null) (null) (null) 3 William H Jefferson Bill (null) Jefferson Thanks
CREATE TABLE #Person
(
ID INT NOT NULL,
DOB DATE NOT NULL
);CREATE TABLE #PersonName
(
ID INT NOT NULL,
PID INT NOT NULL,
FNAME VARCHAR(50) NOT NULL,
MNAME VARCHAR(10) NULL,
LNAME VARCHAR(50) NOT NULL,
NAMETYPE CHAR(1) NOT NULL
);INSERT INTO #Person
(ID, DOB)
VALUES
(1, '2010-01-01'),
(2, '2011-02-01'),
(3, '2009-01-10');INSERT INTO #PersonName
(ID, PID, FNAME, MNAME, LNAME, NAMETYPE)
VALUES
(1, 1, 'James', 'D', 'Doe', 'L'),
(2, 1, 'Jim', NULL, 'Doe', 'C'),
(3, 2, 'Martha', NULL, 'Stu', 'L'),
(4, 3, 'William', 'H', 'Jefferson', 'L'),
(5, 3, 'Bill', NULL, 'Jefferson', 'C');WITH L AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'L'
),
C AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'C'
)
SELECT P.ID,
L.FNAME AS FNAME_L,
L.MNAME AS MNAME_L,
L.LNAME AS LNAME_L,
C.FNAME AS FNAME_C,
C.MNAME AS MNAME_C,
C.LNAME AS LNAME_C
FROM #Person P
LEFT JOIN L
ON L.PID = P.ID
LEFT JOIN C
ON C.PID = P.ID;DROP TABLE #PersonName;
DROP TABLE #Person; -
CREATE TABLE #Person
(
ID INT NOT NULL,
DOB DATE NOT NULL
);CREATE TABLE #PersonName
(
ID INT NOT NULL,
PID INT NOT NULL,
FNAME VARCHAR(50) NOT NULL,
MNAME VARCHAR(10) NULL,
LNAME VARCHAR(50) NOT NULL,
NAMETYPE CHAR(1) NOT NULL
);INSERT INTO #Person
(ID, DOB)
VALUES
(1, '2010-01-01'),
(2, '2011-02-01'),
(3, '2009-01-10');INSERT INTO #PersonName
(ID, PID, FNAME, MNAME, LNAME, NAMETYPE)
VALUES
(1, 1, 'James', 'D', 'Doe', 'L'),
(2, 1, 'Jim', NULL, 'Doe', 'C'),
(3, 2, 'Martha', NULL, 'Stu', 'L'),
(4, 3, 'William', 'H', 'Jefferson', 'L'),
(5, 3, 'Bill', NULL, 'Jefferson', 'C');WITH L AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'L'
),
C AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'C'
)
SELECT P.ID,
L.FNAME AS FNAME_L,
L.MNAME AS MNAME_L,
L.LNAME AS LNAME_L,
C.FNAME AS FNAME_C,
C.MNAME AS MNAME_C,
C.LNAME AS LNAME_C
FROM #Person P
LEFT JOIN L
ON L.PID = P.ID
LEFT JOIN C
ON C.PID = P.ID;DROP TABLE #PersonName;
DROP TABLE #Person;