July 8, 2015 at 9:08 am
Situation:
I got a table with organisation codes with postcal code ranges, from-to.
Example:
Organisationcode, startpostalcode, endpostalcode
001 52005249
I would like to generate rows for this range like this:
001 5200
001 5201
001 5202
.....
001 5249
Can somebody give me the SQL for this 🙂
The table looks like this:
SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation
July 8, 2015 at 9:12 am
kraaitje (7/8/2015)
Situation:I got a table with organisation codes with postcal code ranges, from-to.
Example:
Organisationcode, startpostalcode, endpostalcode
001 52005249
I would like to generate rows for this range like this:
001 5200
001 5201
001 5202
.....
001 5249
Can somebody give me the SQL for this 🙂
The table looks like this:
SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation
You should use a numbers or tally table for this sort of thing. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 8, 2015 at 9:30 am
After you understand how a tally table works, you can play with other variants such as views or functions which might include zero reads.
CREATE FUNCTION dbo.NumbersRange
(
@Start int,
@End int
) RETURNS table WITH SCHEMABINDING
RETURN
WITH E(n) AS(
SELECT 0 FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n) -- 10 rows
),
E2(n) AS(
SELECT a.n FROM E a, E b --10 x 10 = 100 rows
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b --100 x 100 = 10,000 rows
)
SELECT TOP( @End - @Start + 1) -- Return only the necessary rows.
@Start + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E4;
GO
July 8, 2015 at 10:11 am
kraaitje (7/8/2015)
Situation:The table looks like this:
SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation
Gee, that looks more like a query to me, and not a table.
A table would look more like:
CREATE TABLE #DimOrganisation (
OrganisationCode VARCHAR(3),
PostalCodeStart smallint,
PostalCodeEnd smallint);
Not to mention, having that coupled with your sample data:
INSERT INTO #DimOrganisation (OrganisationCode, PostalCodeStart, PostalCodeEnd)
VALUES ('001', 5200, 5249);
Would have made things so much easier for everyone that looks at this and would be tempted to help you (you do know that this is a volunteer site, right? Help us help you... do some of this for us)
Now, like Luis and Sean said, just use a tally table.
Something like:
WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2),
Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions)
SELECT t1.OrganisationCode, t2.N
FROM #DimOrganisation t1
JOIN Tally t2 ON t2.N BETWEEN t1.PostalCodeStart AND t1.PostalCodeEnd
ORDER BY t1.OrganisationCode, t2.N;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2015 at 1:50 am
Thanks a lot, that works fine!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply