how to transfer records to a single record
-
Let's say that I have a table as shown below:
Create table dtl (id Int, fk Int, dtl_note char(30))insert into dtl values
(1,1,'first'),
(1,1, 'second'),
(3,2, 'third'),
(4,2, 'fourth'),
(5,1, 'six')
create table hdr (id int, pk int, notes varchar(max))
What I would like to see in the hdr table is 2 records as follows:
1,1, 'first / second / six '
2,2, 'third / fourth'
How can I combine the info in the dtl records into one record in hdr for each "pk"?
Thanks,
Mike
-
Assuming ID column of hdr table is identity , as u havent given any details about it.
Declare @dtl table (id Int, fk Int, dtl_note varchar(100))
insert into @dtl
values (1,1,'first')
insert into @dtl
values (1,1, 'second')
insert into @dtl
values (3,2, 'third')
insert into @dtl
values (4,2, 'fourth')
insert into @dtl
values (5,1, 'six')
declare @hdr table (id int identity(1,1), pk int, notes varchar(max))
insert into @hdr (pk, notes)
select distinct fk, SUBSTRING((select ' / ' + dtl_note from @dtl where fk = a.fk for XML PATH('')), 4, 1000) as notes
from @dtl a
select * from @hdr
-
INSERT INTO hdr(id,pk,notes)SELECT ROW_NUMBER() OVER(ORDER BY a.fk),
a.fk,
STUFF((SELECT ' / ' + RTRIM(b.dtl_note) AS "text()"
FROM dtl b
WHERE b.fk=a.fk
ORDER BY b.id
FOR XML PATH('')),1,3,'')
FROM dtl a
GROUP BY a.fk;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 (12/15/2010)
INSERT INTO hdr(id,pk,notes)SELECT ROW_NUMBER() OVER(ORDER BY a.fk),
a.fk,
STUFF((SELECT ' / ' + RTRIM(b.dtl_note) AS "text()"
FROM dtl b
WHERE b.fk=a.fk
ORDER BY b.id
FOR XML PATH('')),1,3,'')
FROM dtl a
GROUP BY a.fk;
This is great! One more question... what if I wanted to put a character 13 (Line Feed) instead of the /. How would I do that?
Thanks,
Mike
-
mike 57299 (12/15/2010)
Mark-101232 (12/15/2010)
INSERT INTO hdr(id,pk,notes)SELECT ROW_NUMBER() OVER(ORDER BY a.fk),
a.fk,
STUFF((SELECT ' / ' + RTRIM(b.dtl_note) AS "text()"
FROM dtl b
WHERE b.fk=a.fk
ORDER BY b.id
FOR XML PATH('')),1,3,'')
FROM dtl a
GROUP BY a.fk;
This is great! One more question... what if I wanted to put a character 13 (Line Feed) instead of the /. How would I do that?
Thanks,
Mike
This should do it
SELECT ROW_NUMBER() OVER(ORDER BY a.fk),a.fk,
STUFF((SELECT CHAR(13) + RTRIM(b.dtl_note) AS "text()"
FROM dtl b
WHERE b.fk=a.fk
ORDER BY b.id
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(MAX)'),1,1,'')
FROM dtl a
GROUP BY a.fk;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply