Sybase:如何连接sybase列中的行 [英] Sybase: How do I concatenate rows in sybase column
本文介绍了Sybase:如何连接sybase列中的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有ID和部门的表。
I have a table with ID and Dept.
表是
id dept salary date
1 30 2000 8/25/2015 12:06:54.870 PM
2 20 5500 7/12/2015 12:06:54.870 PM
3 30 6700 11/21/2013 12:06:54.870 PM
4 30 8900 4/16/2009 12:06:54.870 PM
5 30 9900 6/29/2014 12:06:54.870 PM
6 10 1120 7/3/2015 12:06:54.870 PM
7 20 8900 4/13/2013 12:06:54.870 PM
8 10 2400 7/23/2015 12:06:54.870 PM
9 30 2600 8/21/2015 12:06:54.870 PM
10 10 2999 8/3/2015 12:06:54.870 PM
只需要这样的输出
Just need the output like this
Dept ID
30 1,3,4,5,9
推荐答案
这是我所知道的最好方法。如果有人知道更好的解决方案,请发布:
This is the best way I know. Please do post if anyone knows a better solution:
我已将您的表命名为 sal
DECLARE @id INT
, @max INT
, @dep INT
, @all VARCHAR(255)
SELECT @id = 1
, @max = MAX(id)
FROM sal
SELECT * INTO #tmp FROM sal
WHILE (1=1)
BEGIN
SELECT @dep = dept
FROM #tmp
WHERE id = @id
IF @dep IS NULL
BEGIN
SELECT @id = @id + 1
IF @id > @max
BREAK
ELSE
CONTINUE
END
UPDATE #tmp
SET @all = @all + ',' + CONVERT(VARCHAR, id)
WHERE dept = @dep
--remove last comma
select @all = RIGHT(@all, LEN(@all)-1)
DELETE #tmp
WHERE dept = @dep
-- selecting the output. insert into table if you want
SELECT @dep, @all
SELECT @dep = NULL
, @all = NULL
SELECT @id = @id + 1
IF @id > @max
BREAK
-- fail safe
IF @id > 100
BREAK
END
drop table #tmp
这篇关于Sybase:如何连接sybase列中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文