• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

T-SQL Statement

If there is a table with the following structure:

KeyCol            Data
12345            AAABBBCCCDDDEEE
23456            FFFGGGHHHIIIJJJ
34567            QQQWWWEEERRRTTT

How can the data be extracted in the following form?

KeyCol            Data
12345            AAA
12345            BBB
12345            CCC
12345            DDD
12345            EEE
23456            FFF
23456            GGG
<&>
34567            RRR
34567            TTT

Can it be done using a single SQL statement, or does it require multiple tables, loops, or unions?
0
computerstreber
Asked:
computerstreber
  • 4
  • 3
  • 3
  • +1
1 Solution
 
BrandonGalderisiCommented:
Do you want it broken on every 3 characters or every time the letter changes?

You don't really describe your requirements well.
0
 
computerstreberAuthor Commented:
Yes. For example, Row 12345 has a column with the value AAABBBCCCDDDEEE, thus I want to see the row id (12345) and the column, but only the first three. However, I than want to see the second three, the third three, and the fourth three.

12345   AAABBBCCCDDDEEE

OUTPUT:

12345     AAA
12345     BBB
12345     CCC
12345     DDD
12345     EEE
0
 
chapmandewCommented:
are you using 2005?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
computerstreberAuthor Commented:
I am using 2008 and 2005. A solution for either would work.
0
 
Kevin CrossChief Technology OfficerCommented:
As Brandon eluded to we would probably need more details on requirement, but to answer your question I believe this would require some looping and/or union and/or table variable to hold results as you get others so probably not just a single SQL statement although never really tried so may be easier than I think.

One method could be (if you want every three characters grouped):
DECLARE @length INT, @table TABLE(KeyCol BIGINT, Data NVARCHAR(3), SEQ INT)
SET @length = 3
 
WHILE EXISTS (SELECT 1 FROM tablename WHERE LEN(Data) >= @length)
BEGIN
   INSERT INTO @table
   SELECT KeyCol, RIGHT(LEFT(Data, @length), 3), Ceiling(LEN(Data)/@length)+1
   FROM tablename
END
 
SELECT KeyCol, Data
FROM @table
ORDER BY KeyCol, SEQ

Open in new window

0
 
chapmandewCommented:
here is a fancy way to do this if you're using 2005:
--create table #temp(keycol int, data varchar(max))
--
--insert into #temp
--SELECT 123456, 'AAABBBCCCDDDEEE'
--
--insert into #temp
--SELECT 34567,'QQQWWWEEERRRTTT'



;with recurse(keycol, data, datacol, placeholder1, placeholder2)
as
(
      select keycol, data, cast('' as varchar(max)), 1, 3
      from #temp
      union all
      select t.keycol, t.data, substring(r.data, placeholder1, placeholder2), placeholder1 + placeholder2 , placeholder2
      from recurse r
      join #temp t on r.keycol = t.keycol
      where placeholder1 <= len(t.data)
)

select keycol, datacol from recurse
where datacol > ''

0
 
Kevin CrossChief Technology OfficerCommented:
Sorry I didn't see the other posts before submitting mine, so I apologize for duplication.  From your first response, it looks like my example may fit using table variable and while loop.  Added a third column to ensure that we keep in right order.

Forgot to increment and messed up my sequence column.
DECLARE @length INT, @table TABLE(KeyCol BIGINT, Data NVARCHAR(3), SEQ INT)
SET @length = 3
 
WHILE EXISTS (SELECT 1 FROM tablename WHERE LEN(Data) >= @length)
BEGIN
   INSERT INTO @table
   SELECT KeyCol, RIGHT(LEFT(Data, @length), 3), @length/3
   FROM tablename
 
   SET @length = @length + 3
END
 
SELECT KeyCol, Data
FROM @table
ORDER BY KeyCol, SEQ

Open in new window

0
 
BrandonGalderisiCommented:
Try this for starters...
if object_id('[dbo].[fn_FixedLenToTable]') is not null
     drop function [dbo].[fn_FixedLenToTable]
go
create function [dbo].[fn_FixedLenToTable](@DelimitedString nvarchar(max), @Len int)
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@DelimitedString , n, @Len) as string_value
		from	dbo.vw_Nums
		where (n-1)%@Len=0
               and n <= (datalength(@DelimitedString)/@Len)
 
return
end
/*
 
Requires:
create view vw_Nums
as
with
       cte0 as (select 1 as c union all select 1), -- 2
       cte1 as (select 1 as c from cte0 a, cte0 b), -- 4
       cte2 as (select 1 as c from cte1 a, cte1 b), -- 16
       cte3 as (select 1 as c from cte2 a, cte2 b), -- 256
       cte4 as (select 1 as c from cte3 a, cte3 b), -- 65,536
       cte5 as (select 1 as c from cte4 a, cte4 b), -- 4,294,967,296 --four BILLION, not million
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
select * from [dbo].[fn_FixedLenToTable]('a|%25basdf|%25c|%25d','|%25')
 
select theValue from [dbo].[fn_FixedLenToTable]('a','|')
*/
GO
 
select * from [dbo].[fn_FixedLenToTable]('AAABBBCCCDDDEEE',3)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Nice, Tim.
0
 
chapmandewCommented:
mwvisa, thanks.  I try to use CTE's whenever possible.  Not that they're more efficient...they're just more fun to write.  ;)
0
 
BrandonGalderisiCommented:
<hats value="off" recipient="chapmandew" />
0
 
chapmandewCommented:
;)
0
 
computerstreberAuthor Commented:
WOW! This worked perfectly!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now