Need Help in SQL Query - conatinating column values

Question

Post reply

Need Help in SQL Query - conatinating column values

Rajesh Arra

Grasshopper

Points: 10
More actions
May 7, 2010 at 12:44 am

#234673

Hi,
I want to cancatinate column values and display in a single record.
Let me give you in practical
I have data as follows in the table
SalesOrderID CarrierTrackingNumberProductID
43659 4911-403C-98 776
43659 4911-403C-98 777
43659 4911-403C-98 778
43659 4911-403C-98771
43659 4911-403C-98772
43659 4911-403C-98773
need to dispaly as follows
SalesOrderID CarrierTrackingNumberProductID
43659 4911-403C-98 776,777,778,771,...
with out stored procedure,
request you to help me
thanks
Regards
Rajesh A

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should do the trick for you:

DECLARE @TestTable TABLE (

SalesOrderId int,

CarrierTrackingNumber varchar(20),

ProductId int

)

INSERT INTO @TestTable

SELECT 43659, '4911-403C-98', 776

UNION ALL SELECT 43659, '4911-403C-98', 777

UNION ALL SELECT 43659, '4911-403C-98', 778

UNION ALL SELECT 43659, '4911-403C-98', 771

UNION ALL SELECT 43659, '4911-403C-98', 772

UNION ALL SELECT 43659, '4911-403C-98', 773

;WITH DistinctOrders AS (

SELECT DISTINCT SalesOrderId, CarrierTrackingNumber

FROM @TestTable

)

SELECT SalesOrderID, CarrierTrackingNumber,

STUFF

(

SELECT ',' + CAST(ProductId AS varchar(10)) AS [text()]

FROM @TestTable

WHERE SalesOrderID = DO.SalesOrderID

AND CarrierTrackingNumber = DO.CarrierTrackingNumber

FOR XML PATH('')

)

, 1, 1, SPACE(0))

FROM DistinctOrders DO

I know it could look a bit obscure, but, basically it's concatenation through FOR XML PATH('').

Hope this helps

Gianluca

-- Gianluca Sartori

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Gian's code is fast and furios 🙂 that should do!

RAJ GOPAL Newbie Points: 8 More actions · Answer 3

Hi

Refere to this link

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

Rajesh Arra Grasshopper Points: 10 More actions · Answer 4

Thank you all, will work on it and get back to you if needed

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

Rajesh Arra (5/7/2010)
Thank you all, will work on it and get back to you if needed

Better than that, get back to us with how you ended up implementing it. Two way street here and we're all curious. 😉

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

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

RAJ GOPAL (5/7/2010)
Hi
Refere to this link
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

What I really hate about articles like that is it's mostly just a list of methods someone collected and posted without any reasonably sized test data generation and without any test measurements. There's also the comment about seemingly unreliable methods that have been working correctly with the Rushmore Engine (Sybase) long before Microsoft picked it up and called it their own.

It also says things like "This may not be the best performing option, but certain additional tuning could be done to make them suitable for medium sized datasets. " and " but the reader is encouraged to check the execution plans and make any additional tweaks as needed." in reference to recursive CTE's. Why anyone would chose a method that needed to be tuned to handle medium sized datasets when there are much more effecient methods which actually take less code to handle extremely large datasets is beyond me. They also don't tell you what those "certain additional tuning" methods are. You know why? Because there are none for the method in question. 😉

Be careful what you recommend... someone who reads stuff based on your recommendation may work for you someday and then you have some serious back peddling to do.

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