Solved

T-SQL Statement

Posted on 2008-09-30
13
206 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

15 Experts available now in Live!

Get 1:1 Help Now