June 29, 2012 at 2:09 pm
Table 1: this has p_service (eg.5) with 3 rows of text and table 2 has same p_service (eg.5) with 1 record.. We need to get the all the 4 records with line break into table 2
Table 1
key P_ServiceText
1 5 CONDITION OF A/C PWR PLUG, LINE CORD, STRAIN RELIEF
2 5
3 5 CHECK AMPLITUDE ACCURACY. TEST RATE ACCURACY AT 3 POINTS.
1 41333CLEAN VENTILATOR EXTERIOR
2 41333RECORD VENTILATOR HOUR READING: __________
Table 2
P_ServiceText
5 Kris
41333Nak
Result
P_SerciceText
5"CONDITION OF A/C PWR PLUG, LINE CORD, STRAIN RELIEF
CHECK AMPLITUDE ACCURACY. TEST RATE ACCURACY AT 3 POINTS.
Kris"
41333"CLEAN VENTILATOR EXTERIOR
RECORD VENTILATOR HOUR READING: __________
Nak"
June 29, 2012 at 2:58 pm
How do you know what order to concatenate the texts? Or does it matter?
June 29, 2012 at 3:09 pm
You are right.. I have made the changes there is a key column on which it depends on..
hierarchy first from text from table 1 then from table 2 (this does not have a key it is only one record) of them separated with a line break
Thank you for correcting..
Also i have made the changed in the attachment
June 29, 2012 at 4:03 pm
This puts it all together with the like breaks (char(10)), but going through XML changes them to
Hmmmm... have to think on this a little more.
create table #txt1 (
P_Key int not null,
P_Service int not null,
P_Text varchar(max) not null)
insert #txt1 (P_key, P_Service, P_Text) values
(1, 5, 'CONDITION OF A/C PWR PLUG, LINE CORD, STRAIN RELIEF'),
(2, 5,''),
(3, 5, 'CHECK AMPLITUDE ACCURACY. TEST RATE ACCURACY AT 3 POINTS.'),
(1, 41333, 'CLEAN VENTILATOR EXTERIOR'),
(2, 41333, 'RECORD VENTILATOR HOUR READING: __________')
create table #txt2 (
P_Service int not null,
P_Text varchar(max) not null)
insert #txt2 (P_Service, P_Text) values
(5, 'Kris'),
(41333, 'Nak')
SELECT E.P_Service, tasks + x.P_Text
FROM (SELECT DISTINCT P_Service,
(SELECT s2.p_text + CHAR(10) AS 'data()'
FROM #txt1 S2
INNER JOIN #txt2 E2 ON S2.P_Service= E2.P_Service
where e2.P_Service = e.P_Service
order by s2.p_service, s2.P_key
FOR XML PATH('')) tasks
FROM #txt2 E) E join #txt2 x on x.P_Service = e.P_Service
June 29, 2012 at 4:07 pm
David Webb-200187 (6/29/2012)
This puts it all together with the like breaks (char(10)), but going through XML changes them to
Hmmmm... have to think on this a little more.
create table #txt1 (
P_Key int not null,
P_Service int not null,
P_Text varchar(max) not null)
insert #txt1 (P_key, P_Service, P_Text) values
(1, 5, 'CONDITION OF A/C PWR PLUG, LINE CORD, STRAIN RELIEF'),
(2, 5,''),
(3, 5, 'CHECK AMPLITUDE ACCURACY. TEST RATE ACCURACY AT 3 POINTS.'),
(1, 41333, 'CLEAN VENTILATOR EXTERIOR'),
(2, 41333, 'RECORD VENTILATOR HOUR READING: __________')
create table #txt2 (
P_Service int not null,
P_Text varchar(max) not null)
insert #txt2 (P_Service, P_Text) values
(5, 'Kris'),
(41333, 'Nak')
SELECT E.P_Service, tasks + x.P_Text
FROM (SELECT DISTINCT P_Service,
(SELECT s2.p_text + CHAR(10) AS 'data()'
FROM #txt1 S2
INNER JOIN #txt2 E2 ON S2.P_Service= E2.P_Service
where e2.P_Service = e.P_Service
order by s2.p_service, s2.P_key
FOR XML PATH('')) tasks
FROM #txt2 E) E join #txt2 x on x.P_Service = e.P_Service
Not sure if this will work (untested with your code), but it is worth a shot.
create table #txt1 (
P_Key int not null,
P_Service int not null,
P_Text varchar(max) not null)
insert #txt1 (P_key, P_Service, P_Text) values
(1, 5, 'CONDITION OF A/C PWR PLUG, LINE CORD, STRAIN RELIEF'),
(2, 5,''),
(3, 5, 'CHECK AMPLITUDE ACCURACY. TEST RATE ACCURACY AT 3 POINTS.'),
(1, 41333, 'CLEAN VENTILATOR EXTERIOR'),
(2, 41333, 'RECORD VENTILATOR HOUR READING: __________')
create table #txt2 (
P_Service int not null,
P_Text varchar(max) not null)
insert #txt2 (P_Service, P_Text) values
(5, 'Kris'),
(41333, 'Nak')
SELECT E.P_Service, tasks + x.P_Text
FROM (SELECT DISTINCT P_Service,
(SELECT s2.p_text + CHAR(10) AS 'data()'
FROM #txt1 S2
INNER JOIN #txt2 E2 ON S2.P_Service= E2.P_Service
where e2.P_Service = e.P_Service
order by s2.p_service, s2.P_key
FOR XML PATH(''),TYPE).value('.','varchar(max)')) tasks
FROM #txt2 E) E join #txt2 x on x.P_Service = e.P_Service
June 29, 2012 at 4:55 pm
I always learn something new every time I try one of these.
Thanks for jumping in, Lynn.
Had a syntax error on the select, but this runs:
SELECT E.P_Service, tasks + x.P_Text
FROM (SELECT DISTINCT P_Service,
(SELECT s2.p_text + CHAR(13) + CHAR(10) AS 'data()'
FROM #txt1 S2
INNER JOIN #txt2 E2 ON S2.P_Service= E2.P_Service
where e2.P_Service = e.P_Service
order by s2.p_service, s2.P_key
FOR XML PATH(''),TYPE).value('.','varchar(max)') tasks
FROM #txt2 E) E join #txt2 x on x.P_Service = e.P_Service
Lines break correctly when SSMS results are sent to text. Looks like a winner.
July 2, 2012 at 6:58 am
This did work.. 🙂 🙂
This was new learning experience..
Thank you..
July 3, 2012 at 12:47 am
David Webb-200187 (6/29/2012)
I always learn something new every time I try one of these.Thanks for jumping in, Lynn.
Had a syntax error on the select, but this runs:
SELECT E.P_Service, tasks + x.P_Text
FROM (SELECT DISTINCT P_Service,
(SELECT s2.p_text + CHAR(13) + CHAR(10) AS 'data()'
FROM #txt1 S2
INNER JOIN #txt2 E2 ON S2.P_Service= E2.P_Service
where e2.P_Service = e.P_Service
order by s2.p_service, s2.P_key
FOR XML PATH(''),TYPE).value('.','varchar(max)') tasks
FROM #txt2 E) E join #txt2 x on x.P_Service = e.P_Service
Lines break correctly when SSMS results are sent to text. Looks like a winner.
Aaah!!...Very nice David.
I did something like this as well:
Select c.P_Service, '"' + c.Text + ' ' + d.Text + '"' From
(Select Distinct b.P_Service, Replace(STUFF((Select '-' + a.Text From Ex As a Where a.P_Service = b.P_Service For XML Path('')), 1, 1, ''), '-', ' ')
As Text From Ex As b) As c
JOIN
(Select Distinct P_Service, Text From Ex1) As d On c.P_Service = d.P_Service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply