Concatenate multiple rows into single row
Ben Sayer
rows into a one column / one row?
"SELECT person_id, course_ref FROM person, course WHERE person_id =
course_person_id"
(query result)
person_id course_ref
--------- ----------
001 a
001 b
001 c
into
(desired result)
person_id courses_attended
--------- -------------
001 a,b,c
The person id will match to one or more course refs, but i want the end
result to return a single row for each person id, with a single column
(courses_attended) to show all the records that the person id matched
to.
Hope this makes sense - thanks for any responses.
Ben Sayer
Sent via Deja.com http://www.deja.com/
Before you buy.
try...@aol.com
This is easily done using RAC(replacement for Access crosstab query)
that produces all kinds of crosstabs,solves pivoting/concatenation
problems and splits strings.These procedures run on server 7+.You can
check it out at:
www.angelfire.com/ny4/rac
If you want to try the latest full version contact me.
Steve Dassin
try...@aol.com
Keith Kratochvil
I was interested enough in the solutions and the comments to combine them
and put them into VSS.
Here are reposts from many individuals (I hope that they do not mind).
/*
posted by
RAGHU
pat...@ustrotting.com
*/
create table #t2
(c1 int identity , c2 int, c3 varchar(10) )
insert #t2(c2,c3) values(1,"T")
insert #t2(c2,c3) values(1,"h")
insert #t2(c2,c3) values(1,"is")
insert #t2(c2,c3) values(7,"is")
insert #t2(c2,c3) values(7,"easy.")
insert #t2(c2,c3) values(8,"You")
insert #t2(c2,c3) values(8,"will")
insert #t2(c2,c3) values(8,"be")
insert #t2(c2,c3) values(8,"amazed!")
set concat_null_yields_null off
declare @join varchar(2000)
select @join = @join + ' ' + c3 from #t2 A
select LTRIM(@join) AS TheOutput
------
--OR--
------
DECLARE @Everything varchar( 8000 )
SELECT @Everything = ''
/*
UPDATE #t2
SET @Everything = @Everything + c3
*/
select @Everything = @Everything + c3 from #t2
SELECT @Everything
go
drop table #t2
/*
(SQL Server 7.0, SP2)
Heya, all!
I'm trying to take a small record set and create a "text" field out of it
and was hoping to solicit some advice.
Given the following table:
create table TestTable (FieldA int, FieldB int, FieldC int)
I'd like to take the entire contents of table TestTable and create a single
"text" representation of it (this table will always be relatively small).
I've toyed with the possibility of executing BCP via xp_cmdshell and then
reading the file in, but I'm concerned that it might be too costly
(performance- and resource-wise). Speed is of the essence.
The only other alternative that I can come up with is to create a cursor and
loop through the records in the table to append to a string. This, too,
seems kind of costly.
I'm looking for the "text" representation of this result set to have
tab-delimited columns and newline-delimited rows (much like BCP's defaults).
Any ideas or suggestions would be most welcome and very appreciated!
Thanks! :-)
John Peterson
----------------------------------------------------
There is NO GUARANTEE that this will work always. You are relying so
much on the query plan here. ORDER is not guaranteed with the row unless an
ORDER BY is specified. If you do specify an ORDER BY clause, then you get
into other problems.
By design, the variable is supposed to contain the value from the last
row. In this example, since you put an expression like @var = @var + somecol
there is an implicit concatenation. This depends on when SQL Server performs
the scalar expression computation.
Declare this index on the CREATE NONCLUSTERED INDEX i3 ON #t2( c3 ) &
now modify the SELECT to:
declare @join varchar(1255)
select @join = @join + c3
from #t2 A
order by c2
select @join
What do you expect? Concatenation is not possible in this case since the
scalar computation happens before the SORT & the value of the last row is
assigned to the value. There are more cases where this will fail. You CANNOT
RELY on this technique.
For your problem, you need to look at other reliable / predictable
methods. If you can post an example / sample data, we can suggest some other
method.
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)
----------------------------------------------------
Perhaps for your problem, you can do the following. This should work
always & is a T-SQL extension to the UPDATE statement.
DECLARE @Everything varchar( 8000 )
SELECT @Everything = ''
UPDATE tbl
SET @Everything = @Everything + somecol
SELECT @Everything
Note that we do not really update the table.
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)
*/
Joe Celko
>> Is there a way of concatenating the results from one column /
multiple rows into a one column / one row? <<
The LIST([DISTINCT] <string expression>) is part of Sybase's SQL
Anywhere (formerly WATCOM SQL). It is the only aggregate function to
work on character strings. It takes a column of strings, removes the
NULLs and merges them into a single result string having commas between
each of the original strings. The DISTINCT option removes duplicates
as well as NULLs before concatenating the strings together. This
function is a generalized version of concatenation, just as SUM() is a
generalized version of addition.
This is handy when you use SQL to write SQL queries. As one simple
example, you can apply it against the schema tables and obtain the
names of all the columns in a table, then use that list to expand a
SELECT * into the current column list.
One way of doing this query without the WATCOM extension is with scalar
subquery expressions. Assume we have these two tables:
CREATE TABLE People
(id INTEGER NOT NULL PRIMARY KEY,
name CHAR(10) NOT NULL);
INSERT INTO People VALUES (1, 'John');
INSERT INTO People VALUES (2, 'Mary');
INSERT INTO People VALUES (3, 'Fred');
INSERT INTO People VALUES (4, 'Jane');
CREATE TABLE Clothes
(id INTEGER NOT NULL,
seq INTEGER NOT NULL,
item CHAR(10) NOT NULL,
worn CHAR(1) NOT NULL CHECK (worn IN ('Y', 'N')),
PRIMARY KEY (id, seq));
INSERT INTO Clothes VALUES (1, 1, 'Hat', 'Y');
INSERT INTO Clothes VALUES (1, 2, 'Coat', 'N');
INSERT INTO Clothes VALUES (1, 3, 'Glove','Y');
INSERT INTO Clothes VALUES (2, 1, 'Hat', 'Y');
INSERT INTO Clothes VALUES (2, 2, 'Coat', 'Y');
INSERT INTO Clothes VALUES (3, 1, 'Shoes', 'N');
INSERT INTO Clothes VALUES (4, 1, 'Pants', 'N');
INSERT INTO Clothes VALUES (4, 2, 'Socks', 'Y');
Using the LIST() function, we could get an output of the outfits of the
people with the simple query:
SELECT P0.id, P0.name, LIST(item) AS fashion
FROM People AS P0, Clothes AS C0
WHERE P0.id = C0.id
AND C0.worn = 'Y'
GROUP BY P0.id, P0.name;
Result
id name fashion
=======================
1 'John' 'Hat,Glove'
2 'Mary' 'Hat,Coat'
4 'Jane' 'Socks'
To do this without an aggregate function, you must first know the
highest sequence number, so you can create the query. This is a a
simple "SELECT MAX(seq) FROM Clothes" statement in this case, but you
might have to use a COUNT(*) for other tables.
SELECT DISTINCT P0.id, P0.name,
SUBSTRING ((SELECT CASE WHEN C1.worn = 'Y'
THEN (', ' || item) ELSE '' END
FROM Clothes as C1
WHERE C1.id = C0.id
AND C1.seq = 1) ||
(SELECT CASE WHEN C2.worn = 'Y'
THEN (', ' || item) ELSE '' END
FROM Clothes as C2
WHERE C2.id = C0.id
AND C2.seq = 2) ||
(SELECT CASE WHEN C3.worn = 'Y'
THEN (', ' || item) ELSE '' END
FROM Clothes as C3
WHERE C3.id = C0.id
AND C3.seq = 3), FROM 3) AS list
FROM People AS P0, Clothes AS C0
WHERE P0.id = C0.id;
id name list
===========================
1 John Hat, Glove
2 Mary Hat, Coat
3 Fred
4 Jane Socks
Again, the CASE expression on "worn" can be replaced with an "IS NULL"
to replace NULLs with an empty string. If you don't want to see that
Fred is naked -- has an empty string of clothing -- then change the
outermost WHERE clause to read: ...
WHERE P0.id = C0.id AND C0.worn = 'Y';
Since you don't want to see a leading comma, remember to TRIM() it off
or to use the SUBSTRING() function to remove the first two characters.
I opted for the SUBSTRING() because the TRIM() function requires a scan
of the string.
--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.
Timur
(I changed SELECT for simplicity reason)
DECLARE @t varchar(30)
DECLARE @List varchar(7000)
Set @List = ' '
DECLARE C1 CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U'
OPEN C1
FETCH NEXT FROM C1 INTO @t
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @List= @List + ','+ @t
END
FETCH NEXT FROM C1 INTO @t
END
DEALLOCATE C1
Select @List
Timur.
Ben Sayer <ben_m...@my-deja.com> wrote in message
news:8qakgn$qem$1...@nnrp1.deja.com...
> Is there a way of concatenating the results from one column / multiple
> rows into a one column / one row?
>
> "SELECT person_id, course_ref FROM person, course WHERE person_id =
> course_person_id"
>
> (query result)
>
> person_id course_ref
> --------- ----------
> 001 a
> 001 b
> 001 c
>
> into
>
> (desired result)
>
> person_id courses_attended
> --------- -------------
> 001 a,b,c
>
> The person id will match to one or more course refs, but i want the end
> result to return a single row for each person id, with a single column
> (courses_attended) to show all the records that the person id matched
> to.
>
>
> Hope this makes sense - thanks for any responses.
>
> Ben Sayer
>
>