SQL Server 中如何将多行合并为一列? [英] How do you concat multiple rows into one column in SQL Server?

问题描述

我已经四处寻找答案，但我无法弄清楚.我对 SQL Server 比较陌生，还没有完全掌握语法.我有这个数据结构(简化):

<前>表用户"|表标签":UserID 用户名 |TagID 用户ID 照片ID1 鲍勃 |1 1 12 比尔 |2 2 13 简 |3 3 14 山姆 |4 2 2-----------------------------------------------------表照片": |表专辑":PhotoID UserID 相册ID |专辑ID 用户ID1 1 1 |1 12 1 1 |2 33 1 1 |3 24 3 2 |5 3 2 |

我正在寻找一种方法来获取所有照片信息(简单)以及该照片的所有标签，例如 CONCAT(username, ', ') AS Tags 当然与去掉最后一个逗号.我有时间尝试这样做.我已经尝试过 这篇文章 但是当我尝试运行查询时出现错误，说我不能使用 DECLARE 语句... 你们知道如何做到这一点吗?我正在使用 VS08 以及其中安装的任何数据库(我通常使用 MySQL，所以我不知道这到底是什么类型的数据库......它是一个 .mdf 文件?)

解决方案

我想创建一个 UDF:

创建函数 GetTags(PhotoID int) 返回 @tags varchar(max)作为开始声明@mytags varchar(max)设置@mytags = ''select @mytags = @mytags + ', ' + tag from tags where photoid = @photoid返回子字符串(@mytags, 3, 8000)结尾

那么，你所要做的就是:

从照片中选择 GetTags(photoID) 作为 tagList

I've searched high and low for the answer to this, but I can't figure it out. I'm relatively new to SQL Server and don't quite have the syntax down yet. I have this datastructure (simplified):

Table "Users"         | Table "Tags":
UserID    UserName    | TagID    UserID    PhotoID
1         Bob         | 1        1         1
2         Bill        | 2        2         1
3         Jane        | 3        3         1
4         Sam         | 4        2         2
-----------------------------------------------------
Table "Photos":              | Table "Albums":
PhotoID   UserID    AlbumID  | AlbumID     UserID
1         1         1        | 1           1
2         1         1        | 2           3
3         1         1        | 3           2
4         3         2        |
5         3         2        |

I'm looking for a way to get the all the photo info (easy) plus all the tags for that photo concatenated like CONCAT(username, ', ') AS Tags of course with the last comma removed. I'm having a bear of a time trying to do this. I've tried the method in this article but I get an error when I try to run the query saying that I can't use DECLARE statements... do you guys have any idea how this can be done? I'm using VS08 and whatever DB is installed in it (I normally use MySQL so I don't know what flavor of DB this really is... it's an .mdf file?)

解决方案

I'd create a UDF:

create function GetTags(PhotoID int) returns @tags varchar(max)
as
begin
    declare @mytags varchar(max)
    set @mytags = ''

    select @mytags = @mytags + ', ' + tag from tags where photoid = @photoid

    return substring(@mytags, 3, 8000)
end

Then, all you have to do is:

select GetTags(photoID) as tagList from photos

这篇关于SQL Server 中如何将多行合并为一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！