September 8, 2011 at 10:44 am
Hi,
Server: MSSQL 2008 R2
I need to merge data from 2 fields into a single row.
The problem is the fields are in the wrong order so I cant use COALESCE.
Or maybe I can and just dont know how 🙂
Data Table
Customer
Name |Area | City | Department
John, Cape, Newlands, 1
Peter, Natal, Durban, 1
Suzi, Transvaal, Sandton, 2
Output I would like :
(Names and City's for Department 1)
John, Newlands
Peter, Durban
Anyone have any ideas ?
September 8, 2011 at 10:47 am
I use this, then I pivot on the presentation layer.
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
September 8, 2011 at 10:49 am
OOPS I Posted in the Server 2005 area.
Sorry.
Anyway ..
Thanks for the reply. Will try that.
September 8, 2011 at 10:52 am
zaleeu (9/8/2011)
OOPS I Posted in the Server 2005 area.Sorry.
Anyway ..
Thanks for the reply. Will try that.
Won't matter for this one :-).
September 8, 2011 at 11:01 am
Nope, you lost me 🙂
I dont see any place where you mention the name of the table. (Customers)
September 8, 2011 at 11:05 am
zaleeu (9/8/2011)
Nope, you lost me 🙂I dont see any place where you mention the name of the table. (Customers)
Can't write this one for you. While this is a simple concatenation script, you just need to play with it to GET IT.
No way around it. Took me hours to finally understand it. I'm sure you'll be faster than that.
Hopefully you know your own tables / columns so you'll see what each bit does.
September 8, 2011 at 11:10 am
Okay, time to figure this out then.
Cheers !
😛
September 8, 2011 at 11:11 am
zaleeu (9/8/2011)
Okay, time to figure this out then.Cheers !
😛
HTH, I'm not trying to be thick or anything. I just want to make sure you can support that code in production. Better to play with it in dev than in prod at 2 am with the cto calling ever 2 minutes :-D,
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply