Solved

T-SQL Statement

Posted on 2008-09-30
13
207 Views
Last Modified: 2010-04-21
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
Comment
Question by:computerstreber
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22607068
Do you want it broken on every 3 characters or every time the letter changes?

You don't really describe your requirements well.
0
 
LVL 3

Author Comment

by:computerstreber
ID: 22607155
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22607207
are you using 2005?
0
 
LVL 3

Author Comment

by:computerstreber
ID: 22607261
I am using 2008 and 2005. A solution for either would work.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22607292
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22607313
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22607315
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22607317
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22607326
Nice, Tim.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22607334
mwvisa, thanks.  I try to use CTE's whenever possible.  Not that they're more efficient...they're just more fun to write.  ;)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22607406
<hats value="off" recipient="chapmandew" />
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22607440
;)
0
 
LVL 3

Author Closing Comment

by:computerstreber
ID: 31501622
WOW! This worked perfectly!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Group by correlation 4 55
SQL Server 208R2 not recognizing DBF file in linked Server 11 52
SQL Server 2012 express 24 36
SQL Query with Sum and Detail rows 2 41
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now