How to split the batch wise id in commaseparatedly?

Question

How to split the batch wise id in commaseparatedly?

Swamy Magam

Ten Centuries

Points: 1096
More actions
August 5, 2009 at 11:51 pm

#132597

Hi Friends
I have table like
tEmp
Empno EmpName Deptno
101 xyz 10
102 abc 10
103 pqr 20
104 rst 20
105 sdf 30
106 rtu 40
107 abcd 40
for I want output like with table rows
First 3 rows with comma separator then next 3 rows comma separator ................
Empnos
101,102,103
104,105,106
107
Please help me.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Swamy Magam (8/5/2009)
Hi Friends
I have table like
tEmp
Empno EmpName Deptno
101 xyz 10
102 abc 10
103 pqr 20
104 rst 20
105 sdf 30
106 rtu 40
107 abcd 40
for I want output like with table rows
First 3 rows with comma separator then next 3 rows comma separator ................
Empnos
101,102,103
104,105,106
107
Please help me.

I'll trade you a solution for a reason... why do you want to do this. I mean, what is the business reason for wanting to denormalize data like this?

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

Swamy Magam Ten Centuries Points: 1096 More actions · Answer 2

Swamy Magam

Ten Centuries

Points: 1096

August 6, 2009 at 12:20 am

#1035198

yes i want to show a Empnos batch wise .

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

I know what you want to do. I want to know WHY you want to do it. I'd give you the answer up front except no one ever comes back with the reason and I want to know the reason.

--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: 1004406 More actions · Answer 4

Heh... if I knew the reason, we'd be done by now. It is a 2 way street, y'know. Anyway, I don't have the time to wait... it's 2:25 AM here and I have to get some shuteye. If no one else is able to help and you post the reason, I'll get back to this. 😉

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

Swamy Magam Ten Centuries Points: 1096 More actions · Answer 5

Thank u friend. my requirement is I want show batch wise Empnos in grid. this is my Requirement.

David Burrows SSC Guru Points: 65143 More actions · Answer 6

Not wanting to incure Jeff's wrath (let alone pork chop) but I wanted to get his opinion on my solution 😀

SELECT

CAST(MAX(CASE WHEN (RowNo-1)%3=0 THEN Empno ELSE NULL END) as varchar) + ',' +

COALESCE(CAST(MAX(CASE WHEN (RowNo-1)%3=1 THEN Empno ELSE NULL END) as varchar),'') + ',' +

COALESCE(CAST(MAX(CASE WHEN (RowNo-1)%3=2 THEN Empno ELSE NULL END) as varchar),'')

FROM (SELECT Empno,ROW_NUMBER() OVER (ORDER BY Empno) AS [RowNo] FROM #tEmp) a

GROUP BY (RowNo-1)/3

ORDER BY (RowNo-1)/3

This produces the output as stated but it assumes Empno order and 3 Empno per row and no other row output or identifier

Edited: Realised that the OP wanted comma separated output

doh! :blush:

Far away is close at hand in the images of elsewhere.
Anon.

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

Swamy Magam (8/6/2009)
Thank u friend. my requirement is I want show batch wise Empnos in grid. this is my Requirement.

This may be your requirement, but for every requirement, there needs to be a business reason for doing it. That is what Jeff is trying to get you to answer. And, "because that is what the user asked," isn't a valid reason either.

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

David Burrows (8/6/2009)
Not wanting to incure Jeff's wrath (let alone pork chop) but I wanted to get his opinion on my solution 😀

Heh... ok, David. Do you want BBQ or Smoked... 😉

And, I'd still really like an answer to my question as to what the business reason behind this is for two reasons... 1) users who request this type of thing normally don't have a clue as to what they're really asking for and 2) I really want to know the reason behind this particular type of "Death by SQL".

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

David Burrows SSC Guru Points: 65143 More actions · Answer 9

Heh... ok, David. Do you want BBQ or Smoked

Oh definitely BBQ, messier 😛

I am intrigued as to why as well 😀

I only tried a solution because I need the practice and since we are still on 2000 I don't get to write 2005/2008 tsql :crying:

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Heh... but I know how this works... 99% of the time, if you don't get your answer up front, the OP becomes a check valve as soon as he gets his answer.

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