本文发表在 rolia.net 枫下论坛/*
Too bad MS SQL Server does not have an aggregate function
like SUM() that worked on character columns and
concatenated the values.
Instead, we have to use techniques similar to cross tabulation
queries.
Two example algorithms are listed below. One works if there is
a limited number of items to concatenate together, and has good
performance. The other does not have that limitation (although
there is another limitation), but does not perform as well.
The first section below generates random sample data.
*/
-------------------------------------------------
--SAMPLE DATA
-------------------------------------------------
/*
data table columns:
id: object id.
like a person id.
may be multiple records with same id
in the table.
item: value to be concatenated.
each row for a particular id may have
different values for item.
sort_column: sort order.
item's for a particular id will be concatenated
in ascending order of this column.
if sort order not needed, see comments below
for simplifying the select statement.
pk: primary key.
unique identifier for row in table.
The routine below generates sample data for
10 ids with 1 - 4 items per id. Each item
and sort_column value is a random integer 0 - 9.
*/
--create table.
if (object_id("data") is not null) drop table data
go
create table data(id int, item varchar(10),
sort_column int, pk int primary key)
go
--populate table with random data.
set nocount on
declare @id int
declare @cnt int
declare @pk int
select @pk = 0
select @id = 0
while (@id<10)
begin
select @id = @id + 1
select @cnt = floor(rand()*4)+1
while (@cnt > 0)
begin
select @cnt = @cnt - 1
select @pk = @pk + 1
insert into data values (
@id,
convert(varchar, floor(rand()*10)),
floor(rand()*10),
@pk
)
end
end
set nocount off
go
-------------------------------------------------
/*
-------------------------------------------------
SAMPLE DATA
-------------------------------------------------
id item sort_column pk
----------- ---------- ----------- -----------
1 2 1 1
1 5 4 2
1 9 5 3
2 7 1 4
2 6 9 5
3 9 3 6
3 6 9 7
4 1 6 8
4 0 9 9
4 7 4 10
4 6 7 11
5 6 3 12
5 6 2 13
6 5 8 14
6 3 4 15
6 5 1 16
7 3 7 17
7 4 8 18
7 4 4 19
8 9 7 20
8 8 3 21
8 1 9 22
8 9 0 23
9 6 0 24
9 7 0 25
9 2 1 26
10 5 1 27
10 5 3 28
10 3 8 29
(29 row(s) affected)
-------------------------------------------------
DESIRED RESULT FROM SAMPLE DATA
-------------------------------------------------
id items
----------- ----------------------------------------------
1 2, 5, 9
2 7, 6
3 9, 6
4 7, 1, 6, 0
5 6, 6
6 5, 3, 5
7 4, 3, 4
8 9, 8, 9, 1
9 6, 7, 2
10 5, 5, 3
(10 row(s) affected)
-------------------------------------------------
*/
-------------------------------------------------
--LIMITED ROWS PER ID.
-------------------------------------------------
/*
If you can limit the number of items per ID to a small number, you can get
it all into one query, which will probably give you better performance.
Example is for maximum of 4 rows per id. Repeat a "+ isnull(...)" section
up to the maximum number of rows per id, incrementing the n in ") = <n>"
for each section.
If item values can be sorted by pk insted of a sort_column column,
the query can be simplified quite a bit. Change...
| and (
| (data2.sort_column < data1.sort_column)
| or (
| (data2.sort_column = data1.sort_column)
| and (data2.pk <= data1.pk)
| )
| )
...to...
| and (data2.pk <= data1.pk)
...in each "isnull(...)" section.
*/
select
id,
items =
(
select item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 1
)
+ isnull(
(
select ', ' + item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 2
),
'')
+ isnull(
(
select ', ' + item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 3
),
'')
+ isnull(
(
select ', ' + item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 4
),
'')
from
data
group by
id
go
-------------------------------------------------
-------------------------------------------------
--UNLIMITED ROWS PER ID.
-------------------------------------------------
/*
If you want to have an unlimited or high number of items per ID, you
can't avoid using a temp table and looping. However, you can avoid
using a cursor.
This algorithm will work for an unlimited number of items per id.
However, performance is not as good because the routine is procedural,
uses a temp table, cannot be used in a view, and probably slower.
The output of algorithm is still limited by the length of the column
that holds the concatenated data.
If item values can be sorted by pk insted of a sort_column column,
the query can be simplified quite a bit. Change...
| and (
| (data2.sort_column < data1.sort_column)
| or (
| (data2.sort_column = data1.sort_column)
| and (data2.pk <= data1.pk)
| )
| )
...to...
| and (data2.pk <= data1.pk)
...in each "where @cnt = (...)" section.
*/
set nocount on
--temp table to hold results
create table #results (id int, items varchar(40))
--counter variable.
declare @cnt int
set @cnt = 1
--insert id and first item.
insert into #results (id, items)
select
id,
items =
(
select item
from data data1
where @cnt =
(
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
)
and (data1.id = data.id)
)
from data
group by id
--append other items.
while 1=1 --loop until no more updates.
begin
set @cnt = @cnt + 1
update #results
set items = items + ', ' + item
from
#results,
(
select id, item
from data data1
where @cnt =
(
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
)
) afltr
where
afltr.id = #results.id
--break loop if nothing updated.
if @@rowcount = 0 break
end
set nocount off
--display.
select id, items
from #results
order by id
--clean up
drop table #results
go
-------------------------------------------------
"Microsoft" <msullivan@getconnected.com> wrote in message
news:Ou1sEOmsAHA.684@tkmsftngp04...
I am looking to do the following without using a cursor.
I have two tables:
Source (i int, desc varchar(10))
Destination (i int primary key, desc
varchar(2000))
I would like to append all desc of each i value into a second table
A value in Destination may have (0 - n) number of rows in Source.
For example:
Source (1, 'A')
Source (1, 'B')
Source (1, 'Q')
Source (1, 'Z')
Source (2, 'A')
Source (3, 'C')
Source (4, 'B')
Source (4, 'Q')
Would result in:
Destination (1,'ABQZ')
Destination (2,'AC')
Destination (3,'C')
Destination (4,'BQ')
Any ideas?更多精彩文章及讨论，请光临枫下论坛 rolia.net
Too bad MS SQL Server does not have an aggregate function
like SUM() that worked on character columns and
concatenated the values.
Instead, we have to use techniques similar to cross tabulation
queries.
Two example algorithms are listed below. One works if there is
a limited number of items to concatenate together, and has good
performance. The other does not have that limitation (although
there is another limitation), but does not perform as well.
The first section below generates random sample data.
*/
-------------------------------------------------
--SAMPLE DATA
-------------------------------------------------
/*
data table columns:
id: object id.
like a person id.
may be multiple records with same id
in the table.
item: value to be concatenated.
each row for a particular id may have
different values for item.
sort_column: sort order.
item's for a particular id will be concatenated
in ascending order of this column.
if sort order not needed, see comments below
for simplifying the select statement.
pk: primary key.
unique identifier for row in table.
The routine below generates sample data for
10 ids with 1 - 4 items per id. Each item
and sort_column value is a random integer 0 - 9.
*/
--create table.
if (object_id("data") is not null) drop table data
go
create table data(id int, item varchar(10),
sort_column int, pk int primary key)
go
--populate table with random data.
set nocount on
declare @id int
declare @cnt int
declare @pk int
select @pk = 0
select @id = 0
while (@id<10)
begin
select @id = @id + 1
select @cnt = floor(rand()*4)+1
while (@cnt > 0)
begin
select @cnt = @cnt - 1
select @pk = @pk + 1
insert into data values (
@id,
convert(varchar, floor(rand()*10)),
floor(rand()*10),
@pk
)
end
end
set nocount off
go
-------------------------------------------------
/*
-------------------------------------------------
SAMPLE DATA
-------------------------------------------------
id item sort_column pk
----------- ---------- ----------- -----------
1 2 1 1
1 5 4 2
1 9 5 3
2 7 1 4
2 6 9 5
3 9 3 6
3 6 9 7
4 1 6 8
4 0 9 9
4 7 4 10
4 6 7 11
5 6 3 12
5 6 2 13
6 5 8 14
6 3 4 15
6 5 1 16
7 3 7 17
7 4 8 18
7 4 4 19
8 9 7 20
8 8 3 21
8 1 9 22
8 9 0 23
9 6 0 24
9 7 0 25
9 2 1 26
10 5 1 27
10 5 3 28
10 3 8 29
(29 row(s) affected)
-------------------------------------------------
DESIRED RESULT FROM SAMPLE DATA
-------------------------------------------------
id items
----------- ----------------------------------------------
1 2, 5, 9
2 7, 6
3 9, 6
4 7, 1, 6, 0
5 6, 6
6 5, 3, 5
7 4, 3, 4
8 9, 8, 9, 1
9 6, 7, 2
10 5, 5, 3
(10 row(s) affected)
-------------------------------------------------
*/
-------------------------------------------------
--LIMITED ROWS PER ID.
-------------------------------------------------
/*
If you can limit the number of items per ID to a small number, you can get
it all into one query, which will probably give you better performance.
Example is for maximum of 4 rows per id. Repeat a "+ isnull(...)" section
up to the maximum number of rows per id, incrementing the n in ") = <n>"
for each section.
If item values can be sorted by pk insted of a sort_column column,
the query can be simplified quite a bit. Change...
| and (
| (data2.sort_column < data1.sort_column)
| or (
| (data2.sort_column = data1.sort_column)
| and (data2.pk <= data1.pk)
| )
| )
...to...
| and (data2.pk <= data1.pk)
...in each "isnull(...)" section.
*/
select
id,
items =
(
select item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 1
)
+ isnull(
(
select ', ' + item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 2
),
'')
+ isnull(
(
select ', ' + item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 3
),
'')
+ isnull(
(
select ', ' + item
from data data1
where data1.id = data.id
and (
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
) = 4
),
'')
from
data
group by
id
go
-------------------------------------------------
-------------------------------------------------
--UNLIMITED ROWS PER ID.
-------------------------------------------------
/*
If you want to have an unlimited or high number of items per ID, you
can't avoid using a temp table and looping. However, you can avoid
using a cursor.
This algorithm will work for an unlimited number of items per id.
However, performance is not as good because the routine is procedural,
uses a temp table, cannot be used in a view, and probably slower.
The output of algorithm is still limited by the length of the column
that holds the concatenated data.
If item values can be sorted by pk insted of a sort_column column,
the query can be simplified quite a bit. Change...
| and (
| (data2.sort_column < data1.sort_column)
| or (
| (data2.sort_column = data1.sort_column)
| and (data2.pk <= data1.pk)
| )
| )
...to...
| and (data2.pk <= data1.pk)
...in each "where @cnt = (...)" section.
*/
set nocount on
--temp table to hold results
create table #results (id int, items varchar(40))
--counter variable.
declare @cnt int
set @cnt = 1
--insert id and first item.
insert into #results (id, items)
select
id,
items =
(
select item
from data data1
where @cnt =
(
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
)
and (data1.id = data.id)
)
from data
group by id
--append other items.
while 1=1 --loop until no more updates.
begin
set @cnt = @cnt + 1
update #results
set items = items + ', ' + item
from
#results,
(
select id, item
from data data1
where @cnt =
(
select count(*)
from data data2
where (data2.id = data1.id)
and (
(data2.sort_column < data1.sort_column)
or (
(data2.sort_column = data1.sort_column)
and (data2.pk <= data1.pk)
)
)
)
) afltr
where
afltr.id = #results.id
--break loop if nothing updated.
if @@rowcount = 0 break
end
set nocount off
--display.
select id, items
from #results
order by id
--clean up
drop table #results
go
-------------------------------------------------
"Microsoft" <msullivan@getconnected.com> wrote in message
news:Ou1sEOmsAHA.684@tkmsftngp04...
I am looking to do the following without using a cursor.
I have two tables:
Source (i int, desc varchar(10))
Destination (i int primary key, desc
varchar(2000))
I would like to append all desc of each i value into a second table
A value in Destination may have (0 - n) number of rows in Source.
For example:
Source (1, 'A')
Source (1, 'B')
Source (1, 'Q')
Source (1, 'Z')
Source (2, 'A')
Source (3, 'C')
Source (4, 'B')
Source (4, 'Q')
Would result in:
Destination (1,'ABQZ')
Destination (2,'AC')
Destination (3,'C')
Destination (4,'BQ')
Any ideas?更多精彩文章及讨论，请光临枫下论坛 rolia.net