Hi,
I have table below:
ID Postcode PostcodeNext
1 LL11
2 LL12
3 LL13
4 LL14
5 LL15
6 LL16
.
.
.
I need to update PostcodeNext column for each row in database with next 5 postcodes as string, so ie value ID 1 would be:
ID Postcode PostcodeNext
1 LL11 LL12,LL13,LL14,LL15,LL16
Did you try this ?
Select *
, stuff(
(
select rtrim(T2.postcd) + ', '
from #tmp T2
where T2.idnr between T1.idnr + 1 and T1.idnr + 5
order by T2.idnr
for xml path('P'), root('R'), type
).query('R/P').value('.', 'varchar(64)'), 1, 0, '')
from #tmp T1
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 20, 2021 at 12:18 pm
Hi Johan,
How do i get #tmp T1 and #tmp T2? and also once i got string, how do i update that PostcodeNext field for all rows?
My table looks like this
CREATE TABLE [dbo].[postcode](
[id] [int] IDENTITY(1,1) NOT NULL,
[Postcode] [varchar](50) NULL,
[PostcodeNext] [varchar](100) NULL,
CONSTRAINT [PK_postcode_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
October 20, 2021 at 1:26 pm
PostCodeNext is not long enough to hold 5 PostCodes, commas and spaces.
October 20, 2021 at 1:28 pm
LL12,LL13,LL14,LL15,LL16 has only 25 characters, column can hold up to 100, even if it was short, i could increase it, i just need to know how to update that column with next 5 postcodes
October 20, 2021 at 1:37 pm
PostCode is varchar(50). 50 x 5 = 250 plus commas ( and spaces ?).
#TMP is your table name. Johan did not know the name of your table at the time.
Using Johan's code, perhaps you can use it to build an update statement, based on the results it gives.
October 20, 2021 at 1:39 pm
Postcode is 50, but actual string never will be more that 4 digits/letters
October 20, 2021 at 1:40 pm
Never say "Never" ! Why is postcode varchar(50) ?
Also, Doesn't seem like a good idea to have the table name the same as a column name.
October 20, 2021 at 1:42 pm
its just how it was designed, but this is not a problem, that string in Postcode column will not be bigger than 4 characters, i just need script to update, length is irrelevant here
October 20, 2021 at 1:56 pm
Johan's code assumes postcode is entered sequentially and uses ID to order them.
But if LL15 was Inserted before LL14 then you will get different results than you want because the ID for LL15 will be lower then LL14's ID.
In that case, you could copy the records into a temp table with an identity field, ordered by postcode, so they are both in sequence.
Then use the temp table to update the live table.
October 20, 2021 at 2:18 pm
Hi Johan, script worked well, but how can i remove last comma (,), ideally i dont want last one showing
October 20, 2021 at 2:22 pm
Select *
, stuff( ( select rtrim(T2.Postcode) + CASE WHEN T2.id = T1.id + 5 then '' else ', ' end
from Postcodetbl T2
where T2.id between T1.id + 1 and T1.id + 5
order by T2.id
for xml path('P'), root('R'), type
).query('R/P').value('.', 'varchar(64)'), 1, 0, '')
from postcodetbl T1
October 20, 2021 at 5:03 pm
Since you're on SQL Server 2019, you shouldn't need to use XML and STUFF etc. any more - you can use the STRING_AGG function, which became available with SQL Server 2017. That will make it a little more straightforward, I think.
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15
However, I don't have access to that version, so I can't provide a direct answer as to how the actual query would be like for your use case.
October 20, 2021 at 5:59 pm
Please select Johan's post as the correct answer (not mine). He did all the work.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply