Row Concat Problem

[bdukes]

Here is the current layout of the table I'm working with:

Row_ID Line DX_ID
123 1 a
123 2 b
123 3 c
534 1 a
534 2 b
945 1 a
945 2 b
945 3 c
945 4 d

What I want to end up within in a View or temporary table that looks like this:

Row_ID DX_ID
123 a b c
534 a b
945 a b c d

Essentially I'm wanting to concatenante all of the DX_ID column values together that have the same Row_ID value. In this example, an individual Row_ID can have one to many rows.

Ideas on how to accomplish this?

Thanks,
B

 

[hmckillop]

DECLARE @v_Temp Table (row_id int, dx_id varchar(100))

INSERT INTO @v_Temp (row_ID, DX_ID)
SELECT distinct(Row_id), '' FROM MyTable

UPDATE tmp
SET tmp.DX_ID = tmp.DX_ID + tb1.DX_ID + ' '
FROM @v_Temp tmp
INNER JOIN Mytable tb1 on tb1.row_id = tmp.row_id

SELECT * From @v_temp

I think this should work, havent tested it yet


"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

it doesnt work, sry will post again in 1 min

"I'm living so far beyond my income that we may almost be said to be living apart

 

[amrita418]

You can use this the following approach but will have to create more max(case) entries if the number of descriptions per ID are more than 5.

create table #TestDukes (id int, description char(1))

insert into #TestDukes values(123,               'a')
insert into #TestDukes values(123,         'b')
insert into #TestDukes values(123,         'c')
insert into #TestDukes values(534,          'a')
insert into #TestDukes values(534,          'b')
insert into #TestDukes values(945,         'a')
insert into #TestDukes values(945,         'b')
insert into #TestDukes values(945 ,          'c')
insert into #TestDukes values(945 ,          'd' )

select         p.id, 
        isnull(max(case  p.count when 1 then p.description end), '')
        + isnull(max(case  p.count when 2 then p.description end), '')
        + isnull(max(case p.count when 3 then p.description end), '')
        + isnull(max(case p.count when 4 then p.description end), '')
        + isnull(max(case p.count when 5 then p.description end), '')
from         (select a.id id, description = a.description + ' ', count(b.id) count
from         #TestDukes a,
        #TestDukes b
where         a.id = b.id
        and a.description >= b.description
group by    a.id, a.description) p
group by     p.id

Regards,
AA

 

[bdukes]

Because the amount of rows per ROW_ID will be unpredicatable (including the maximum number of rows), I was hoping to find a solution that would loop through all of the rows for a given ROW_ID and then concatenate the DX_ID values together.

B

 

[amrita418]

Here is the bare bones code that loops through. I am sure you can tune it.

DECLARE
	@v_string VARCHAR(200),
	@v_id INT,
	@v_description VARCHAR(10),
	@v_old_id INT

DECLARE C1 CURSOR FOR SELECT 		ID, 
					DESCRIPTION
              	      FROM 		#TestDukes
              	      ORDER BY 		ID, 	
					DESCRIPTION

OPEN 	C1

--DROP 	TABLE #TempData

CREATE 	TABLE #TempData (Id int, Descipt VARCHAR(200))

SET 	@v_id = NULL
SET 	@v_old_id = 0
SET 	@v_description = NULL
SET 	@v_string = ''

FETCH 	NEXT FROM C1 INTO @v_id, @v_description 

WHILE (@@FETCH_STATUS = 0) 
BEGIN

	IF  (@v_old_id = @v_id) 
		SET @v_string = @v_string + @v_description + ' '
	IF  (@v_old_id != @v_id) 
	BEGIN
		IF (@v_old_id != 0)
		INSERT INTO #TempData VALUES (@v_old_id, @v_string)
		set @v_string = '' + @v_description + ' '
		set @v_old_id = @v_id
	END

SET 	@v_id = NULL
SET 	@v_description = NULL

FETCH 	NEXT FROM c1 INTO @v_id, @v_description
END

INSERT 	INTO #TempData VALUES (@v_old_id, @v_string)
CLOSE 	C1
DEALLOCATE C1

Do a select on the Temp table to see the output.
Also, I am inserting the data into a temp table instead you can print the output.


Regards,
AA