Split the data without loop

Question

Split the data without loop

NewBeeSQL

SSCrazy

Points: 2170
More actions
March 24, 2009 at 2:04 pm

#75442

Hello Team,
In the old design, i have the data in a table like below:
ID value
1 1
2 3-4-5
3 1-4
Now as per new design, i have to move the data in the following fashion.
id value
1 1
2 3
2 4
2 5
3 1
3 4
Can there be any update without looping?
--Regards
raj
-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Do you have a Numbers or Tally table? If so, then yes, it's quite easy to split without a loop. Actually, it's faster than it would be with a loop.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

I have a table where in i have all the values concatenated with "-"..now i have to split them into multiple rows to insert into a new table..

Please let me know the code to do this..

TIA

Raj

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You misunderstood what GSquared was asking you. In my signature block below, the third article link talks about tally tables. Read the article, it will provide you with the information you are seeking to accomplish the task before you. If, after reading the article, you still have questions just let us know.

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 4

Yup..I got what tally table means.Thats Awesome...It rocks...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

In case you don't know which link to go check out - here's Jeff Moden's "tips and tricks" article on various creative ways yo use a Tally table:

http://www.sqlservercentral.com/articles/TSQL/62867/[/url]

The last example should directly apply to your situation at hand.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 6

Thanks matt..I got the Tally Concept**

Now for learning Sake....How would you accomplish reverse of this?

I mean to say..

If the table looks Like

ID -- value

1 1

1 2

1 3

2 4

2 5

3 7

3 8

Then if we have to build something like

ID -- value

1 1,2,3

2 4,5

3 7,8

How to accomplish?

--Raj

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I'd like to verify something first. You have posted this question in a SQL Server 7, 2000 forum, ar you using SQL Server 2000 or SQL Server 2005?

The answer can be different depending on what version you are using.

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 8

SQL SERVER 2000...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 9

IF OBJECT_ID('tempdb..#TT') <> 0

Drop Table #TT

Declare @CombinedValue varchar(255)

CREATE TABLE #TT (Col1 int, Col2 varchar(2))

Insert Into #TT Values (1,'1')

Insert Into #TT Values (1,'2')

Insert Into #TT Values (2,'1')

Insert Into #TT Values (2,'2')

SELECT @CombinedValue = coalesce(@CombinedValue + ',' + Col2, Col2)

FROM #TT

WHERE Col1 = 1

SELECT @CombinedValue

How about this? .. Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Phil Factor SSC-Insane Points: 20244 More actions · Answer 10

For the definitive answer on how to do the reverse, read this.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Best wishes,
Phil Factor

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 11

Mohit...The code you gave doesnt work here i believe...

My direct expected result should be in table as below:

ID -- value

1 1,2

2 1,2

Hope you got the difference?

_raj

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

samyshekhar Grasshopper Points: 13 More actions · Answer 12

I wana solve same problem.Any help would be appreciated.

i have the data in a table like below:

ID value

1 1

2 3-4-5

3 1-4

Now as per new design, i have to move the data in the following fashion.

id value

1 1

2 3

2 4

2 5

3 1

3 4

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

You can find the answer in the following article:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63503 More actions · Answer 14

Once you have the DelimitedSplit8K function in your database, you can do something like this:

SELECT test.ID

, test.value

, split.ItemNumber

, Item = split.Item

FROM

(SELECT 1 AS ID,'1' AS Value

UNION ALL

SELECT 2, '3-4-5'

UNION ALL

SELECT 3, '1-4') test

CROSS APPLY dbo.DelimitedSplit8K(test.value,'-') split

Except in the query inside the parentheses, you would include the primary key of your table and the column to be split.