need help transforming a table's column to rows

Question

Post reply

need help transforming a table's column to rows

ktlady

SSCommitted

Points: 1509
More actions
June 20, 2009 at 2:42 pm

#133425

I have a table (tableA) with following format while Column1 is primary key and Column2 is delimited with ":".
Column1 Column2
a1 1:3:5:6
a2 2:4:5
I'd like to use stored procedure to transform tableA to tableB and insert into tableB as following:
Column1 Column2
a1 1
a1 3
a1 5
a1 6
a2 2
a2 4
a2 5
In case of tableB, the primary key would become Column1 & Column2.
Could someone help me with the T-SQL stored procedure code in handling this?
Thanks a lot!

Viewing 15 posts - 1 through 15 (of 20 total)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

You can use a Tally table and CROSS APPLY to scan your text and split for your delimiter.

DECLARE @t TABLE (col1 varchar(10), col2 varchar(30));

INSERT INTO @t

SELECT 'a1', '1:3:5:6'

UNION ALL SELECT 'a2', '2:4:5';

; WITH t1 (col1, col2) AS

(

SELECT

col1,

':' + col2 + ':'

FROM @t

)

SELECT

t1.col1,

t2.Item

FROM t1

CROSS APPLY

(

SELECT

SUBSTRING(t1.col2, N + 1, CHARINDEX(':', t1.col2, N + 1) - N - 1) Item

FROM Tally

WHERE N < LEN(t1.col2)

AND SUBSTRING(t1.col2, N, 1) = ':'

) t2;

If you don't know a Table or how to work with please search this site. You will find a really good article published by Jeff Moden which explains the Tally table and how to use it for requirements like this.

Flo

WayneS SSC Guru Points: 95409 More actions · Answer 2

ktlady (6/20/2009)
I have a table (tableA) with following format while Column1 is primary key and Column2 is delimited with ":".
Column1 Column2
a1 1:3:5:6
a2 2:4:5
I'd like to use stored procedure to transform tableA to tableB and insert into tableB as following:
Column1 Column2
a1 1
a1 3
a1 5
a1 6
a2 2
a2 4
a2 5
In case of tableB, the primary key would become Column1 & Column2.
Could someone help me with the T-SQL stored procedure code in handling this?
Thanks a lot!

You can use some xml to do the trick...

-- first make a temp table to hold the sample data

declare @Tmp table (

ColumnA char(2),

ColumnB varchar(50),

TmpCol XML NULL) --<<<<< NOTE the new column being added

-- put the sample data into the temp table.

-- NOTICE how this makes it so much easier for people to just copy and start testing

insert into @Tmp (ColumnA, ColumnB)

select 'a1', '1:3:5:6' UNION ALL

select 'a2', '2:4:5'

-- update the xml column by replacing the delimiter with XML tags,

-- and putting the appropriate XML tags around the string.

update @Tmp

set TmpCol = '' + replace(ColumnB, ':', '') + ''

-- shred the xml data apart into individual rows

select T.ColumnA,

x.data.value('.','int') AS ColumnB

--INTO TABLE2

from @Tmp T

CROSS APPLY TmpCol.nodes('/rows/row') AS x(data)

results:

ColumnAColumnB
a1 1
a1 3
a1 5
a1 6
a2 2
a2 4
a2 5

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ktlady SSCommitted Points: 1509 More actions · Answer 3

Flo & Wayne,

Thank you so much! You guys are so quick and I'll try out both ways. Flo's way is more straight-forward to me. I appreciate Wayne's xml way and all it's comments too.

It's a great learning experience from you guys!

Have a great weekend!

ktlady SSCommitted Points: 1509 More actions · Answer 4

Sorry, I got errors from both scripts. Would you please help again.

The first Flo's script, I got the following error:

Invalid object name 'Tally'.

Second Waynes script, I got empty output with only headers

"ColumnA ColumnB"

Thanks again!

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

Florian Reischl (6/20/2009)
If you don't know a Table or how to work with please search this site. You will find a really good article published by Jeff Moden which explains the Tally table and how to use it for requirements like this.
Flo

Thanks for the plug, Flo. 🙂 The article on what a Tally table is and how it replaces a loop in many cases, can be found at the following URL...

http://www.sqlservercentral.com/articles/T-SQL/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)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

As a side bar, for those interested in a Tally table solution that will work in virtually any release of SQL Server...

[font="Courier New"]--===== Build the test table as the data source

CREATE TABLE dbo.TableA (Column1 VARCHAR(5), Column2 VARCHAR(30))

INSERT INTO dbo.TableA

(Column1, Column2)

SELECT 'a1', '1:3:5:6' UNION ALL

SELECT 'a2', '2:4:5'

--===== Solution for virtually any version of SQL Server

INSERT INTO dbo.TableB

(Column1, Column2)

SELECT a.Column1,

SUBSTRING(a.Column2, t.N+1, CHARINDEX(':', a.Column2, N+1) - N-1) AS Column2

FROM dbo.Tally t

CROSS JOIN

(SELECT Column1, ':'+Column2+':' AS Column2 FROM dbo.TableA) a

WHERE N < LEN(a.Column2)

AND SUBSTRING(a.Column2, N, 1) = ':'

[/font]

--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)

ktlady SSCommitted Points: 1509 More actions · Answer 7

Jeff, thanks for the pointer. Sorry that I missed it the first time. It sure is a great article! There is so much to learn for SQL server!

Wayne or anyone: How come the 2nd xml script doesn't work? It looks perfectly logical to me. How come I am not getting any output?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

ktlady (6/20/2009)
Jeff, thanks for the pointer. Sorry that I missed it the first time. It sure is a great article! There is so much to learn for SQL server!

It's ok... most folks don't usually have a Tally table when they first hear of it and don't understand that you need to build one. Most think that it comes with SQL Server. 😛

Thanks for the feedback on the article, KT... I appreciate it.

--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)

WayneS SSC Guru Points: 95409 More actions · Answer 9

Jeff Moden (6/20/2009)
It's ok... most folks don't usually have a Tally table when they first hear of it and don't understand that you need to build one. Most think that it comes with SQL Server. 😛

You know, as much as it helps out, maybe it ought to. Put a million (edit: or billion) row tally table in master, and people wouldn't have an excuse... :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 10

ktlady (6/20/2009)
Sorry, I got errors from both scripts. Would you please help again.
The first Flo's script, I got the following error:
Invalid object name 'Tally'.
Second Waynes script, I got empty output with only headers
"ColumnA ColumnB"
Thanks again!

I just copied the code I pasted up earlier and tried it out... it works fine for me. I'm not sure why it's not working for you. Is anyone else having a problem with it?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ktlady SSCommitted Points: 1509 More actions · Answer 11

By the way, i am using SS2008 Management Studio. Don't know if that would make any difference.

WayneS SSC Guru Points: 95409 More actions · Answer 12

ktlady (6/20/2009)
By the way, i am using SS2008 Management Studio. Don't know if that would make any difference.

I don't think so... that's what I'm using also.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ktlady SSCommitted Points: 1509 More actions · Answer 13

Wayne, I added "select * from @Tmp" at end of your code. And here is the result I get from.

ColumnAColumnBTmpCol

a11:3:5:61356

a22:4:5245

Does this match the expectation?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

WayneS (6/20/2009)
Jeff Moden (6/20/2009)
It's ok... most folks don't usually have a Tally table when they first hear of it and don't understand that you need to build one. Most think that it comes with SQL Server. 😛
You know, as much as it helps out, maybe it ought to. Put a million (edit: or billion) row tally table in master, and people wouldn't have an excuse... :w00t:

Heh... they kind of did :hehe: ... take a look at the NUMBER column of Master.dbo.spt_Values where the TYPE = 'P'. I don't believe they ever intended it to be used as a short Tally table, but it is there.

I also don't mind telling people about the Tally table. If MS put one in Master, you'd still have to explain what it is and how it works. To wit, I'd rather point to my own article than a BOL article. 😀

As a sidebar and if I recall correctly, MySQL actually has a "sequence" function that you can join to. I don't know how it's doing now, but I remember it as being horribly slow. Slow or not, people are thinking about it.

--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)