Traversing Rows

Question

Traversing Rows

karthik M

One Orange Chip

Points: 29139
More actions
September 10, 2008 at 7:41 am

#382612

Hi All,
I have one senario.
ID Value
1MAGFX
150
1Comment1
2MALOX
250
2Comment2
3MAGFX
350
3Comment3
I want to display like
1 MAGFX 50 Comments1
2 MAGFX 50 Comments2
3 MAGFX 50 Comments3
No of rows may increas , but the column count is constant i.e 3 only.
I know i haven't provided the table structure. But please understand it is very urgent, So can you please create the table structure ?
Inputs are highly appreciable !
karthik

Viewing 9 posts - 1 through 9 (of 9 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

How can we reliably tell whether a text Value is a product name or a comment?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

karthik M One Orange Chip Points: 29139 More actions · Answer 2

it is comment section, User will enter like this only, based on the comment i have split.

I reached some point, But i am unable to do it successfully.

Declare @holdingAndWeightAndComments varchar(255)

Select @holdingAndWeightAndComments = '@MAGFX~50~Comment1@MALOX~50~Comment2@MAGFX~50~Comment3@'

Select ID = IDENTITY(5),substring(@holdingAndWeightAndComments,N+1,

charindex('@',substring(@holdingAndWeightAndComments,N+1,dataLength(@holdingAndWeightAndComments)))-1) As hold

into #hold

from Tally -- Tally Table is a direct replacement of Loop.

Where N < dataLength(@holdingAndWeightAndComments)

and substring(@holdingAndWeightAndComments,N,1) = '@'

SELECT ID,'pct001','B',SUBSTRING('~'+mh.hold+'~',N+1,

CHARINDEX('~',substring('~'+mh.hold+'~',N+1,dataLength('~'+mh.hold+'~')))-1) AS Value

--into #FinalResult

FROM dbo.Tally t,#hold mh

WHERE N < DATALENGTH('~'+mh.hold+'~')

AND SUBSTRING('~'+mh.hold+'~',N,1) = '~'

I have written the above code for splitting the above string.

My requirement is,

@MAGFX~50~Comment1@MALOX~50~Comment2@MAGFX~50~Comment3@

User will give like this

First i have to split it based on @ symbol.

My first query did that and i got the below result.

MAGFX~50~Comment1

MAGFX~50~Comment2

MAGFX~50~Comment3

Again ,I need to split the above values like

MAGFX 50 Comment1

MAGFX 50 Comment2

MAGFX 50 Comment3

My second query did that like

1MAGFX

150

1Comment1

2MALOX

250

2Comment2

3MAGFX

350

3Comment3

Now i need to convert these rows into column.

This is my requirement.

Pls give me some quick reply.

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

karthikeyan (9/10/2008)
Hi All,
I have one senario.
ID Value
1MAGFX
150
1Comment1
2MALOX
250
2Comment2
3MAGFX
350
3Comment3
I want to display like
1 MAGFX 50 Comments1
2 MAGFX 50 Comments2
3 MAGFX 50 Comments3
No of rows may increas , but the column count is constant i.e 3 only.
I know i haven't provided the table structure. But please understand it is very urgent, So can you please create the table structure ?
Inputs are highly appreciable !

Nope. That's the bit you do. We do the difficult bit 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Like this?

[font="Courier New"]DECLARE @holdingAndWeightAndComments VARCHAR(60)

SELECT @holdingAndWeightAndComments = '@MAGFX~50~Comment1@MALOX~50~Comment2@MAGFX~50~Comment3@'

DROP TABLE #hold

SELECT number, '9' AS [ID], --[ID] = IDENTITY(5),

SUBSTRING(@holdingAndWeightAndComments,number+1,

CHARINDEX('@',SUBSTRING(@holdingAndWeightAndComments,number+1,DATALENGTH(@holdingAndWeightAndComments)))-1) AS hold

INTO #hold

FROM Numbers -- Tally Table is a direct replacement of Loop.

WHERE number < DATALENGTH(@holdingAndWeightAndComments)

AND SUBSTRING(@holdingAndWeightAndComments,number,1) = '@'

SELECT [ID],

hold,

CHARINDEX('~', hold, 1) AS [first ~],

CHARINDEX('~', hold, CHARINDEX('~', hold, 1)+1) AS [second ~],

SUBSTRING(hold, 1, CHARINDEX('~', hold, 1)-1) AS [First Element], --(could use LEFT here)

SUBSTRING(hold, CHARINDEX('~', hold, 1)+1,

CHARINDEX('~', hold, CHARINDEX('~', hold, 1)+1) - CHARINDEX('~', hold, 1)-1) AS [Second Element],

SUBSTRING(hold, CHARINDEX('~', hold, CHARINDEX('~', hold, 1)+1)+1, 10) AS [Third Element]

FROM #hold[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Or even better - one tally table for each delimiter 😛

[font="Courier New"]DECLARE @holdingAndWeightAndComments VARCHAR(60)

SET @holdingAndWeightAndComments = '@MAGFX~51~Comment1@MALOX~52~Comment2@MAGFX~53~Comment3@'

SELECT n1.number,

n2.number,

CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1),

CHARINDEX('@', @holdingAndWeightAndComments, n1.number+1),

SUBSTRING(@holdingAndWeightAndComments, n1.number+1, n2.number - n1.number-1) AS Column1,

SUBSTRING(@holdingAndWeightAndComments, n2.number+1, CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1) - n2.number-1) AS Column2,

SUBSTRING(@holdingAndWeightAndComments, CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1)+1,

CHARINDEX('@', @holdingAndWeightAndComments, n1.number+1)-CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1)-1) AS Column3

FROM Numbers n1, Numbers n2

WHERE n1.number <= DATALENGTH(@holdingAndWeightAndComments)

AND SUBSTRING(@holdingAndWeightAndComments,n1.number,1) = '@'

AND n2.number <= DATALENGTH(@holdingAndWeightAndComments)

AND SUBSTRING(@holdingAndWeightAndComments,n2.number,1) = '~'

AND n2.number BETWEEN n1.number AND CHARINDEX('@', @holdingAndWeightAndComments, n1.number+1)

AND CHARINDEX('@', @holdingAndWeightAndComments, n1.number+1) > CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1)

AND CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1) > 0[/font]

Output:

Number1 Number2 ~ pos @ pos Column1 Column2 Column3

----------- ----------- ----------- ----------- ---------- ---------- ----------

1 7 10 19 MAGFX 51 Comment1

19 25 28 37 MALOX 52 Comment2

37 43 46 55 MAGFX 53 Comment3

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

If two tally tables makes it too confusing, then one will do at a push. The output of this is the position of the first @, the first and second ~, and the last @, per row. These numbers are used to LEFT or SUBSTRING the original string to obtain the elements.

[font="Courier New"]DECLARE @holdingAndWeightAndComments VARCHAR(60)

SET @holdingAndWeightAndComments = '@MAGFX~51~Comment1@MALOX~52~Comment2@MAGFX~53~Comment3@'

SELECT CHARINDEX('@', @holdingAndWeightAndComments, n.number) AS [@1],

CHARINDEX('~', @holdingAndWeightAndComments, n.number) AS [~1],

CHARINDEX('~', @holdingAndWeightAndComments, CHARINDEX('~', @holdingAndWeightAndComments, n.number)+1) AS [~2],

CHARINDEX('@', @holdingAndWeightAndComments, n.number+1) AS [@2]

FROM Numbers n

WHERE n.number <= DATALENGTH(@holdingAndWeightAndComments)

AND SUBSTRING(@holdingAndWeightAndComments,n.number,1) IN ('~', '@')

AND CHARINDEX('~', @holdingAndWeightAndComments, n.number+1) < CHARINDEX('@', @holdingAndWeightAndComments, n.number+1)

AND CHARINDEX('@', @holdingAndWeightAndComments, n.number+1) > CHARINDEX('@', @holdingAndWeightAndComments, n.number)[/font]

Output:

@1 ~1 ~2 @2

-------- -------- -------- --

1 7 10 19

19 25 28 37

37 43 46 55

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Chris,

Thanks a lot. I appreciate your timely help !

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

You're welcome, Karthik. Thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden