Converting large Column into Rows

Question

Post reply

Converting large Column into Rows

John Hines-433198

SSC Rookie

Points: 32
More actions
September 30, 2008 at 10:48 am

#193354

Hi,
I'm trying to convert some denormalized COBOL data into a SQL Server 2005 table. I'm hoping for an elegant solution, because I already have a brute force solution.
Here's a sample table:
KeyCol DataCol
12345 AABBCC
23456 BBCCDD
34567 WWEEGG
I'd like a query to return data in the following format:
KeyCol DataCol
12345 AA
12345 BB
12345 CC
23456 BB
34567 EE
34567 GG
I've done it using separate queries with UNION ALL, and I can see how it could be done with a cursor. What I'm wondering is if there is simple trick in 2005 which I haven't learned yet.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 1

You can do this with a tally table (see the excellent article by Jeff Moden for more detail).

Create the tally table, if you don't already have one:

select top 10000 identity(int, 1, 1) num

into tally

from master..spt_values v1

cross join master..spt_values v2

alter table tally add primary key clustered (num)

Now a very simple select, joining to the tally table, will normalise your data:

create table test_table (KeyCol int, DataCol varchar(50))

insert test_table

select 12345, 'AABBCC'

union all

select 23456, 'BBCCDD'

union all

select 34567, 'WWEEGG'

select KeyCol, substring(DataCol, 2 * t.num - 1, 2)

from test_table tt

join tally t on t.num <= len(DataCol) / 2

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Well done, Richard! 🙂

Just a suggestion... you may be able to get a wee bit more speed out of it if you eliminate as many of the math functions as you can. For example...

SELECT KeyCol, SUBSTRING(DataCol,t.num,2) AS DataCol

FROM Test_Table tt

JOIN Tally t

ON t.num%2 = 1

AND t.num <= LEN(DataCol)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

John, the article that Richard was referring to (thank you for the kudo, Richard) is located at the following URL...

[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font]

http://www.sqlservercentral.com/articles/TSQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Hines-433198 SSC Rookie Points: 32 More actions · Answer 4

Wow. You guys rock!

I've never seen this "Tally Table" technique before, but I like it. I will add it to my toolbox.

Thanks for the help and the link to the article. Searching on this site had yielded a lot of posts which were similar to my question, but I couldn't figure out how to make the apply to me.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

Thanks for the feedback, John. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)