October 29, 2011 at 2:06 am
create table tempdocinfo (irecordid int,
instrument_type_abbreviation varchar(250),
sbooktype varchar(250),
sbookno varchar(250),
pageno varchar(250),
recording_suffix varchar (250),
recorded_date varchar(250),
Chain varchar(250),
party varchar(250),
property varchar(250))
create table tempgrantor (irecordid int,
partyid int,
Last_Name varchar(250),
grantorfirst varchar(250),
grantormiddle varchar(250))
insert into tempdocinfo values (1, 'R', NULL, NULL, NULL, '2440774', '2011-09-01', 'false', 'false', 'true')
insert into tempgrantor values (1, 1, 'GITTINGS', 'JAMES', 'S')
insert into tempdocinfo values (2, 'DT', NULL, NULL, NULL, '2440846', '2011-09-01', 'false', 'false', 'true')
insert into tempgrantor values (2, 2, 'ANDERS', 'JEANETTE', NULL)
insert into tempgrantor values (2, 3, 'ANDERS', 'KENNETH', 'D')
----------------
I Need the output as follows:
irecordid recording_suffix, grantorFirst1, last_name1, granterFirst2, last_name2,... granterFirstn, last_nameN
12440774JAMESGITTINGS
22440846JEANETTEANDERS KENNETH ANDERS
I wrote the following query but it's giving me rows in multiple lines for which there are multiple grantors. I need all granters to be in same line, either in separate columns or concatenated as one column (separated by any character)
select td.irecordid, td.recording_suffix,
tg.grantorfirst, tg.last_name
from tempdocinfo td
inner join tempgrantor tg
on td.irecordid=tg.irecordid
12440774JAMESGITTINGS
22440846JEANETTEANDERS
22440846KENNETHANDERS
October 31, 2011 at 4:36 pm
This is a good little trick if you want all values concatenated for a related ID:
selecttd.irecordid
,td.recording_suffix
,grantors =
stuff((
select ', ' + tg.grantorfirst + ' ' + tg.Last_Name
from dbo.tempgrantor as tg
where tg.irecordid = td.irecordid
order by tg.Last_Name, tg.grantorfirst
for xml path ('')), 1, 2, '')
from dbo.tempdocinfo as td
If you need the values broken into different columns that is possible but takes a different approach.
October 31, 2011 at 11:26 pm
Thank you. This is very handy trick. Will learn more about xml 🙂
November 1, 2011 at 1:43 am
bteraberry (10/31/2011)
This is a good little trick if you want all values concatenated for a related ID:
selecttd.irecordid,td.recording_suffix
,grantors =
stuff((
select ', ' + tg.grantorfirst + ' ' + tg.Last_Name
from dbo.tempgrantor as tg
where tg.irecordid = td.irecordid
order by tg.Last_Name, tg.grantorfirst
for xml path ('')), 1, 2, '')
from dbo.tempdocinfo as td
If you need the values broken into different columns that is possible but takes a different approach.
The example, while good, is missing a "final touch": if the text in the columns contains any of the xml-prohibited characters like for example &, < and > these will be escaped as & amp ;, & lt ;, and & gt ; (without the spaces, I added these here to avoid the forum software replacing the escape sequences by the original character). The same example that does handle these characters correctly:
selecttd.irecordid
,td.recording_suffix
,stuff((
select ', ' + tg.grantorfirst + ' ' + tg.Last_Name as [text()]
from dbo.tempgrantor as tg
where tg.irecordid = td.irecordid
order by tg.Last_Name, tg.grantorfirst
for xml path (''), type).value('.','varchar(256)'), 1, 2, '') as grantors
from dbo.tempdocinfo as td
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply