Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

spliting data rowwise Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 1:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:35 AM
Points: 13, Visits: 50

create table #temp
(
range2 varchar(15),
descrip varchar(10)
)

insert into #temp
select '10-13','test one'
union
select 'T100-T1105','test two'
union
select '20G-22G','test three'

select * from #temp

output required

create table #temp1
(
range2 varchar(15),
descrip varchar(10)
)

insert into #temp1
select '10','test one'
union
select '11','test one'
union
select '12','test one'
union
select '13','test one'
union
select 'T100','test two'
union
select 'T101','test two'
union
select 'T102','test two'
union
select 'T103','test two'
union
select 'T104','test two'
union
select 'T105','test two'
union
select '20G','test three'
union
select '21G','test three'
union
select '22G','test three'

select * from #temp1


input
select * from #temp

output required
select * from #temp1
Post #1566250
Posted Wednesday, April 30, 2014 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 7,094, Visits: 6,913
;WITH cte (prefix,suffix,leftpart,rightpart,descrip) AS (
SELECT CASE WHEN LEFT(range2,1) LIKE '[a-zA-Z]' THEN LEFT(range2,1) ELSE '' END,
CASE WHEN RIGHT(range2,1) LIKE '[a-zA-Z]' THEN RIGHT(range2,1) ELSE '' END,
LEFT(range2,CHARINDEX('-',range2)-1),
SUBSTRING(range2,CHARINDEX('-',range2)+1,255),
descrip
FROM #temp),
cte2 (prefix,suffix,startno,endno,descrip) AS (
SELECT prefix,suffix,
CAST(REPLACE(REPLACE(leftpart,prefix,''),suffix,'') as int),
CAST(REPLACE(REPLACE(rightpart,prefix,''),suffix,'') as int),
descrip
FROM cte)
SELECT prefix+CAST(startno+N as varchar(10))+suffix,descrip
FROM cte2
JOIN master.dbo.Tally t ON t.N BETWEEN 0 AND endno-startno




Far away is close at hand in the images of elsewhere.

Anon.

Post #1566263
Posted Wednesday, April 30, 2014 2:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 995, Visits: 3,015
Hi

Here's my attempt at it. I am making some assumptions about your data that you will need to take into account.
1. There will only be up to a single letter at either the end or the beginning
2. That letter will be the same on both sides of the hyphen
3. The number range will always be low to high

It will also work with range values like '31J' and 'S2-S2'

WITH parseRange AS (
SELECT num1 = cast(replace(rng1,letter,'') as int),
num2 = cast(replace(rng2,letter,'') as int),
prefix = case when ltrsuffix=0 then letter else '' end,
suffix = case when ltrsuffix=1 then letter else '' end,
descrip
FROM (
SELECT rng1 = left(range2, len(range2) - charindex('-',reverse(range2)))
,rng2 = right(range2, len(range2) - charindex('-',range2))
,letter = substring(range2,patindex('%[^0-9-]%', range2),1)
,ltrsuffix = cast(patindex('%[^0-9-]%', range2) - 1 as bit)
,descrip
from #temp
) r
),
CTETally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e1(N),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e2(N),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e3(N),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e4(N)
)
SELECT prefix + CAST(N + num1 - 1 AS VARCHAR(10)) + suffix, descrip
FROM parseRange p
CROSS APPLY (SELECT TOP (num2 - num1 + 1) N FROM CTETally) t;

Post #1566606
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse