Find all the matches for col1 in col2 and display athe matches in col2 as column seperated values

Question

Post reply

Find all the matches for col1 in col2 and display athe matches in col2 as column seperated values

Alicia Rose

Ten Centuries

Points: 1044
More actions
November 6, 2007 at 1:51 pm

#183612

Hi sql gurus,
I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name .
The tables below might give a better idea
current scenario:
Product Name SubmissionID columnC Column D
AAA 123
AAA 456
BBB 111
ccc 121
AAA 789
Expected result:
Product Name SubmissionID columnC Column D
AAA 123,456,789
BBB 111
CCC 121
Any suggestions/pointers would be highly appreciated.
Alicia Rose

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

If you have a table that looks kinda like this called "yourtable"...

CREATE TABLE yourtable (ProductName VARCHAR(10), SubmissionID INT)

INSERT INTO yourtable

(ProductName,SubmissionID)

SELECT 'AAA',123 UNION ALL

SELECT 'AAA',456 UNION ALL

SELECT 'BBB',111 UNION ALL

SELECT 'ccc',121 UNION ALL

SELECT 'AAA',789

...then, create a function that looks like this...

CREATE FUNCTION dbo.fnConcatSubmissionID (@ProductName AS VARCHAR(10))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Return VARCHAR(8000)

SELECT @Return = ISNULL(@Return +',' , '') + CAST(SubmissionID AS VARCHAR(10))

FROM yourtable

WHERE ProductName = @ProductName

RETURN @Return

END

GO

... and do a SELECT like this...

SELECT DISTINCT

ProductName,

dbo.fnConcatSubmissionID(ProductName) AS SubmissionIDs

FROM yourtable

ORDER BY ProductName

... and, yes, you can add almost as many columns as you'd like to the SELECT list...

--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)
Intro to Tally Tables and Functions

Alicia Rose Ten Centuries Points: 1044 More actions · Answer 2

Hi Jeff,

Thanks a lot for the reply.

I am in a similar situation again.

I have a table that has columns 1)ProductName and 2)qualification.

The reporting scenario is like i have to display the highest qualification among all qualifications for the particular product name:

Suppose 3 qualifications(B,P,D)

D__lowest

B__Medium

P__highest

So i need P always if P is there among the group.

The different conditions would be

1)If the ProductName has only only type of qualification then display that.

2)If both B and D, then display only B.

3)If both P and D, then display only p.

4) If p,B and D then display only P.

The current table would be like:

ProductName QualificationCol3Col4

P1B

P1P

P1D

P2B

P2D

P3P

P3D

P4D

P5B

The expected Result:

ProductName QualificationCol3Col4

P1P

P2B

P3P

P4D

P5B

Any pointers would be highly appreciated

Alicia Rose

John Rowan SSC Guru Points: 56440 More actions · Answer 3

I'm not sure how that is similar, but here's this should be continued in your other post here:

http://qa.sqlservercentral.com/Forums/Topic421242-338-1.aspx

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I don't see any similarity, either, except for the fact that Alicia has, again, not shown anything that she's tried (not to mention the double post).

--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)
Intro to Tally Tables and Functions