flattening data

Question

Post reply

flattening data

martin.scullion

Grasshopper

Points: 10
More actions
January 24, 2008 at 5:17 am

#183246

Hi there
I'm sure this is easy but I'm battling with it, anyway this is what I would like to achieve.
I have a very badly designed table such that there are recordIDs and Keys, recordIDs may be duplicated, keys are unique ie : -
RecordID Key
1234 23
2345 45
2345 46
2345 47
9876 90
9876 91
I wish to 'flatten' the data out so I can return the following recordset : -
1234 23
2345 45 46 47
9876 90 91
Can someone point me in the right direction please, I'm on SQL server 2005
Many thanks - Martin

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

It's not clear whether you want the keys concatenated into one column or left as separate columns.

This will concatentate the keys

SELECT a.RecordID,

(SELECT b.[Key] AS "data()"

FROM mytable b

WHERE b.RecordID=a.RecordID

ORDER BY b.[Key]

FOR XML PATH('')) AS [Keys]

FROM (SELECT DISTINCT RecordID FROM mytable) a

ORDER BY a.RecordID

____________________________________________________

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

martin.scullion Grasshopper Points: 10 More actions · Answer 2

Absolutely spot on, exactly what I have been looking for many thanks

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

Just an FYI... The original design wasn't bad... 2nd normal form 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)

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

.....Which also might be a necessary "evil" if the table is a "mapping" table (the intermediate table used to create many to many relations). Of course if would then be better if it had its own unique identifier.

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