TSQL Puzzle

Question

Post reply

TSQL Puzzle

Viewing 6 posts - 1 through 7 (of 7 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Sound like row concatenation

Try this link[/url]

Clear Sky SQL
My Blog[/url]

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 2

Try changing

a.ballotcommitteename b. ballotcommitteename

to

a.ballotcommitteename < b. ballotcommitteename

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 3

Thanks Guys.

Hey Mark I changed what you told me and it worked so far. However the problem is I don't know how many time's I'll have to add the column values to the roll.

Some users can have only one value, some two values, some three, and so on.

Damn this is hard

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 4

Something like this then

WITH CTE AS (

SELECT pa_contactidname,asmemberof,ballotcommitteename,

ROW_NUMBER() OVER(PARTITION BY pa_contactidname,asmemberof ORDER BY ballotcommitteename) AS rn

FROM user_table)

SELECT pa_contactidname,asmemberof,

MAX(CASE WHEN rn=1 THEN ballotcommitteename END) AS ballotcommitteename1,

MAX(CASE WHEN rn=2 THEN ballotcommitteename END) AS ballotcommitteename2,

MAX(CASE WHEN rn=3 THEN ballotcommitteename END) AS ballotcommitteename3

FROM CTE

GROUP BY pa_contactidname,asmemberof

If you don't know the maximum number of ballotcommitteename in advance, you'll have

to construct the query dynamically

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 5

You are the man. It worked however I do need to do it dynamically because I don't know the maximum number of ballotcommitteename in advance.

CTE is way new to me.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

http://www.sqlservercentral.com/articles/cross+tab/65048/

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