Concatenate data from different rows

[netangel]

I need to concatenate data (strings) from <> rows. My table looks something like:

1 'X'
1 'YR'
2 'FI'
2 'E'
2 'AAA'

And I need a select statment that returns
1 'X YR'
2 'FI E AAA'.

I don't know how many rows each number has, and I don't know the size of the original string.

I think this can't be done with SQL, but I'd prefer to avoid doing it on my application code (with a for each statment) or with cursors. It has to be a single SELECT statment.

HELP!!!


NetAngel

 

[grega]

I suspect you're right - it can't be done in a single select :-(

Greg.

 

[netangel]

The main reason I want it in a single select statment is because it has to work under sql server 7/2000, sybase 5/8 and oracle 8/9.

This is why I don't want cursors.

But if anyone has an magical idea to solve it with more than a select, and if it works on all engines, I'd love to hear it.
NetAngel

 

[Swany]

just out of curiousity is SQLJ supported on all those platforms? If you can write a stored SQLJ procedure to do it that would be your best bet, since it will work on all three databases (assuming it is supported in all three)

alternatively:

In oracle to use a single SQL query you could probably use a rollup/cube query to do what you need to do. What you are trying to do is basically create what is generically called a &quot;crosstab query&quot;.

I am 99% sure that MSSQL supports doing crosstabs in a single query format, but I am not very familiar with the product. I think it is called a TRANSFORM query or something like that.

I don't think sybase supports crosstabs at all. .
.. Eat, think and be merry .
... ....................... .

 

[tlbroadbent]

Native T-SQL in SQL Server doesn't support crostabs. You can create stored procedures or purchase third party SW. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[wossname]

You could try to use this method. It involves using an initial insert into a temporary table of the values that you want to concatenate plus their keys. Then you progressively load into your destination table and delete from the temporary table until you run out of rows to load.

The example below works in SQL2000 and uses table variables for speed. However, you can modify it to use temporary tables. The statements will have to be placed in a stored procedure or, if you use temp tables, can be run as a sequence of ANSI-SQL statements within code.

I hope that this helps.

declare @temptablecount int

declare @destTable table 
    (keyCol int,
	concatValue VARCHAR(3000),
    PRIMARY KEY(keyCol))

declare @temptable table 
   (keyCol int,
	sourceValue VARCHAR(30),
    PRIMARY KEY
       (keyCol,
        sourceValue))

-- Load your temp table with the source values and keys.
-- Replace NULLS with '' for consistency.
insert into @temptable
select distinct keyCol, ISNULL(sourceValue, '')
from yourDB..yourTable
order by keyCol

-- Load your destination table with the bulk of your source rows.
insert into @desttable
select keyCol, min(ltrim(rtrim(sourceValue))) 
from @temptable
group by keyCol

-- Now remove them from your temporary table.
delete @temptable 
from @temptable a 
inner join
   (select keyCol, min(sourceValue) as sourceValue
    from @temptable
    group by keyCol) b
on a.keyCol = b.keyCol
and a.sourceValue = b.sourceValue

select @temptablecount = count(*) from @temptable

while @temptablecount > 0
begin
    update a
    set a.concatValue = 
        CASE 
            WHEN a.sourceValue = '' THEN
                ltrim(rtrim(b.sourceValue))
            ELSE 
                a.concatValue + ', ' + ltrim(rtrim(b.sourceValue))
        END
    from @desttable a
    inner join 
       (select keyCol, min(sourceValue) as sourceValue
        from @temptable
        group by keyCol) b
    on a.keyCol = b.keyCol

    -- Now remove them from your temporary table.
    delete @temptable 
    from @temptable a 
    inner join
       (select keyCol, min(sourceValue) as sourceValue
        from @temptable
        group by keyCol) b
    on a.keyCol = b.keyCol
    and a.sourceValue = b.sourceValue
    
    select @temptablecount = count(*) from @temptable
end

 

[SantaMufasa]

If you decide that your only alternative is to loosen some of your restrictions, then you could build in each of your database platforms (Oracle, Sybase, MSSQL, et cetera) a SQL-executable function such as the one I did here for Oracle:

select * from yada;

ID TEXT
---------- --------------------
1 X
1 YR
2 FI
2 E
2 AAA

5 rows selected.

create or replace function crosstab (id_in in number) return varchar2 is
cursor tab is select text from yada where id = id_in;
str varchar2(2000);
begin
for r in tab loop
str := str||' '||r.text;
end loop;
return ''''||trim(str)||'''';
end;
/
col x format a20
select id, crosstab(id) x from yada group by id, crosstab(id);

ID X
---------- --------------------
1 'X YR'
2 'FI E AAA'

2 rows selected.

Cheers,

Dave Hunt