Concatenate column rows into one row with a group by

Question

Post reply

Concatenate column rows into one row with a group by

Viewing 5 posts - 16 through 19 (of 19 total)

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

pam.neeley Valued Member Points: 56 More actions · Answer 1

Try this

Select a.grp,sum(a.amount), b.ledgers From tbl a

join (

SELECT c.grp, ledgers = c.ledger + ',' + c.ledger

FROM tbl c ) b on a.grp = b.grp

Group by a.grp, b.ledgers

Mel3405 SSCommitted Points: 1904 More actions · Answer 2

I thought it might work but it didn't. Going back to the initial table we created, the result set is this.

grpledgers

1A01,A01

1A02,A02

1A03,A03

2A22,A22

3A33,A33

3A36,A36

Thank you for your time. All your tips were very helpfull and won't go to waste. I convinced my boss not to concanate.

Thank you so much.

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

MelissaLevitt (3/10/2010)
I convinced my boss not to concanate.

Why? It's about the only option you have in 2000. Use the function I demo'd in the article for 2000.

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

Mel3405 SSCommitted Points: 1904 More actions · Answer 4

The function works perfectly but I can't use it because I have to work off of a temp table.

This data (the ledger info) will be used in a drill through report so the data can be displayed concatenated or not.

I did learn a lot from your article and it definately answered my question.

Thank you.

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

Dang... you're right... I forgot about the temp table.

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