Concatenate
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
Software/Hardware used:
Software/Hardware used:
ASKED:
September 25, 2008 2:37 PM
UPDATED:
September 25, 2008 4:23 PM
RELATED QUESTIONS
- timestamp and comparing different tables
- How to concat SQL Server 2005 data with line feed
- DataGridView Visual Studio 2005 VB.NET Windows Forms programmatically editing the datagrid
- Dynamic Column name in a Cursor
- SQL update command to update certain digits in a row of a column with VARCHAR2(9) DATATYPE.
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
