Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Eliminate duplicate ID by concatenating string values from rows, where all columns except first one are dynamic Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 11:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:25 PM
Points: 10, Visits: 107
I have a table as follow-:

TranID Field1 Field2
1 f2
1 f1
2 2f2
2 2f1

where field1, field2 are dynamic columns of type varchar
There can be any number of columns Field1, Field2, Field3 so on

My problem is, I want to remove repeated transactionid and show only once as below-:

TranID Field1 Field2
1 f1 f2
2 2f1 2f2

Can anybody help me out on this as I am unable to figure out a way to handle this in dynamic columns.

Thanks in advance.

Post #777712
Posted Wednesday, August 26, 2009 11:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 715, Visits: 2,706
Look up "FOR XML" in BOL, it will help you concatenate those strings together, based on the TranID.

Hope that helps,


Cheers,

J-F
Post #777717
Posted Wednesday, August 26, 2009 10:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:25 PM
Points: 10, Visits: 107
Thanks J-F.

I think this approach can solve my problem listed earlier.

But I have come across another problem. When I try excuting following query on PUB database its showing me only half output. I can't see all rows converted to xml. M I misssing anything?

Query-:
SELECT store.stor_id as Id, stor_name as Name, sale.ord_num as OrderNo,
sale.qty as Qty
FROM stores store inner join sales sale on store.stor_id = sale.stor_id
ORDER BY stor_name
FOR XML AUTO

Output-:

I think editor will not show xml output I pasted under output.


Could you please suggest here.
Post #778039
Posted Thursday, August 27, 2009 7:09 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 715, Visits: 2,706
Well actually, since you are on SQL server 2000, the solution will not work, it would have implied you can use for xml path(''), and it appeared in sql 2005...

Really sorry for the mislead.


Cheers,

J-F
Post #778255
Posted Thursday, August 27, 2009 7:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:57 AM
Points: 1,949, Visits: 8,304
This is all you need to know about concatenating strings

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/




Clear Sky SQL
My Blog
Kent user group
Post #778263
Posted Monday, August 31, 2009 11:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:25 PM
Points: 10, Visits: 107
I am facing few problems as all the columns except one are dynamic. But I am working on that.

Thanks a lot.

Post #780094
Posted Monday, August 31, 2009 11:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:25 PM
Points: 10, Visits: 107
Hi,

Above mentioned query is given expected output in sql 2000 too.

I modified few settings in query analyzer in order to increase the maximum characters per column for result set and it worked.

But it’s not working for dynamic column query of my application.
I am trying to make it work.

Thanks
Post #780100
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse