adding rownumbers in a continuous fashion to returned rows

Question

adding rownumbers in a continuous fashion to returned rows

sql_learner29

SSCrazy Eights

Points: 9107
More actions
April 16, 2012 at 9:43 am

#267448

Hi,
I have a result set that identifies the employee info in each row...like this:
StartDocument
PETER
123 AVE
NJ
USA
EndDocumrnt
StartDocument
RAY
555 AVE
PA
06666
EndDocumrnt
and so on...
so i know that there are 7 lines dedicated for each employee info,, I need to pivot it by numbering it continuously like this
1 StartDocument
2 PETER
3 123 AVE
4 NJ
5
6 USA
7 EndDocumrnt
1 StartDocument
2 RAY
3 555 AVE
4 PA
5 06666
6
7 EndDocumrnt
How to achieve this kind of numbering as Row_Number is not working in this case
Any help on this??
Thanks for your help...
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

With 314 points and 791 visits, i hope you know the etiquettes in posting the sample data in readily consumable format. Please follow Jeff's article http://www.sqlservercentral.com/articles/Best+Practices/61537/ and post your data.

FYI, i know how to use produce your expected result! Time crunch, so need readily consuable data!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

Here is the create and insert script

create table TEST

(

Name varchar(100)

)

insert into test

select 'StartDocument' union all

select 'PETER' union all

select '123 AVE' union all

select 'NJ' union all

select '' union all

select 'USA' union all

select 'EndDocumrnt' union all

select 'StartDocument' union all

select 'RAY' union all

select '555 AVE' union all

select 'PA' union all

select '06666' union all

select '' union all

select 'EndDocumrnt'

select * from test

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Phil Parkin SSC Guru Points: 246991 More actions · Answer 3

That does not look like a real-world table to me!

What is the PK?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

I had some spare time now and here is the query

First, sample data

DECLARE @tab TABLE

(

iD INT IDENTITY(1,1)

,Data VARCHAR(100)

)

INSERT @tab

SELECT 'StartDocument'

UNION ALL SELECT 'PETER'

UNION ALL SELECT '123 AVE'

UNION ALL SELECT 'NJ'

UNION ALL SELECT ''

UNION ALL SELECT 'USA'

UNION ALL SELECT 'EndDocument'

UNION ALL SELECT 'StartDocument'

UNION ALL SELECT 'RAY'

UNION ALL SELECT '555 AVE'

UNION ALL SELECT 'PA'

UNION ALL SELECT '06666'

UNION ALL SELECT ''

UNION ALL SELECT 'EndDocument'

Then the T-SQL code

; WITH CTE AS

(

SELECT T.iD , T.Data ,1 AS GrpNo

FROM @tab T

WHERE T.iD = 1

UNION ALL

SELECT Base.iD , Base.Data

, CASE WHEN C.Data = 'EndDocument' AND Base.Data = 'StartDocument'

THEN C.GrpNo + 1

ELSE C.GrpNo

END

FROM CTE C

INNER JOIN @tab Base

ON C.iD + 1 = BASE.iD

)

SELECT C.iD , C.Data , C.GrpNo

, RN = ROW_NUMBER() OVER(PARTITION BY C.GrpNo ORDER BY C.iD)

FROM CTE C

ORDER BY C.iD

And the results:

iDDataGrpNoRN

1StartDocument 11

2PETER 12

3123 AVE 13

4NJ 14

5 15

6USA 16

7EndDocument 17

8StartDocument 21

9RAY 22

10555 AVE 23

11PA 24

1206666 25

13 26

14EndDocument 27

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

its a raw data coming from old source file..

this is my source ...no PK is defined here in the file

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Phil Parkin SSC Guru Points: 246991 More actions · Answer 6

Learner1 (4/16/2012)
its a raw data coming from old source file..
this is my source ...no PK is defined here in the file

OK - what method are you using to read the file data?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 7

@cold coffee...Thanks a lot ...it works ............

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 8

@phil.

I am directly using ssis to dump the file data into tables...

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 9

There is a problem but when the data exceeds 100 rows it fails ( my file contains 80,000 rows)

INSERT into @tab(data)

select top 109 Name from import4

error

(109 row(s) affected)

Msg 530, Level 16, State 1, Line 11

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

is there anyway to tackle it...

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

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

Learner1 (4/16/2012)
@cold coffee...Thanks a lot ...it works ............

Just be careful with this. You will have to ensure that SSIS actually assigns the ID's in the correct order (if they're not assigned in the same order as the incoming rows from your file, ColdCoffee's script will fail).

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 11

Matt Miller (#4) (4/16/2012)
Learner1 (4/16/2012)
@cold coffee...Thanks a lot ...it works ............
Just be careful with this. You will have to ensure that SSIS actually assigns the ID's in the correct order (if they're not assigned in the same order as the incoming rows from your file, ColdCoffee's script will fail).

And also, each logical group starts with StartDocument and ends with EndDocument , even spellings should be correct!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 12

SSIS sequence and spell check is correct but the failure is caused by > 100 rows recursion..

Error:

(109 row(s) affected)

Msg 530, Level 16, State 1, Line 11

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

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

Assuming you can get the ID's assigned correctly, here's the CTE with no recursion:

with startCTE as (

select ROW_NUMBER() over (order by ID) grp,

ID startID

from #tab

where [data]='StartDocument'),

EndCTE as (

select ROW_NUMBER() over (order by ID) grp,

id endid

from #tab

where [data]='EndDocument'),

StartEndCTE as (

select startCTE.grp,

startID,

endID

from startCTE join EndCTE on startCTE.grp = EndCTE.grp)

select grp,

ID-startID+1 RN,

t.*

from #tab t join startendCTE s on t.id between startID and endID

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

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 14

Thanks a lot...it works too...

thank you both of you..................

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]