Concatenate

5 pts.
Tags:
Concatenate
T/SQL
I have a value in a column that I need combined on 1 line. I need a new row when the Id in the table changes. For example Column A Column B 1 Boyd 1 Honaker 2 Steel I need the row to be 1 Boyd, Honaker 2 Steel I can get the name column to concatenate but I get one large line. I can't figure out the second part of the equation. I'm using TSQL

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you don’t know how many times a value in column A will repeat, I think you may need to write a stored procedure.

Something like the following:

<pre>CREATE PROCEDURE TEST
AS
DECLARE @cola int, @colb varchar(20), @data varchar(200)
DECLARE c CURSOR
FOR
SELECT DISTINCT column_a
FROM table_x
OPEN c
FETCH NEXT FROM c into @cola
WHILE @@FETCH_STATUS = 0
BEGIN
set @data = @cola
DECLARE c2 CURSOR
FOR
SELECT column_b
FROM table_x
WHERE column_a = @cola
OPEN c2
FETCH NEXT FROM c2 into @colb
WHILE @@FETCH_STATUS = 0
BEGIN
set @data = @data + “, ” + @colb
FETCH NEXT FROM c2 into @colb
END
– Do something
PRINT @data
CLOSE C2
DEALLOCATE C2
FETCH NEXT FROM c into @cola
END
CLOSE c
DEALLOCATE c
GO</pre>

The same could be achieved with just 1 cursor, but I think it is understandable using 2.

Hope this helps.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following