将多行合并为一行

Question

我正在尝试不同的JOIN查询，但没有得到想要的结果。

我有2张桌子：

Table 1: **StockItemShort**

ItemID  | Code          | Name    
 432724 | CK002-16-09   | Green Daisy Pearl Earrings
 432759 | CK002-16-149  | Emerald Crystal Centre Daisy Earrings

Table 2: **StockItemCatSearchValueShort**

ItemID |    SearchValueID
432724 |    388839
432724 |    389061
432724 |    390269
432724 |    389214
432759 |    388839
432759 |    389051
432759 |    390269
432759 |    389214

我找不到想要的结果。

我想得到以下结果：

ItemID  | Code  | Name  | SearchValueID | SearchValueID | SearchValueID | SearchValueID 
432724  | CK002-16-09   | Green Daisy Pearl Earrings    | 388839    | 389061    | 390269    | 389214
432759  | CK002-16-149  | Emerald Crystal Centre Daisy Earrings | 388839    | 389051    | 390269    | 389214

Answer 1

您不能具有这样的动态列数，但是可以 将数据连接 成字符串：

select
    s.ItemID, s.Code, s.Name,
    stuff(
        (
            select ', ' + CAST(sv.SearchValueID AS VARCHAR)
            from ItemSearch as sv
            where sv.ItemID = s.ItemID
            for xml path(''), type
        ).value('.', 'nvarchar(128)')
    , 1, 2, '') as SearchValues
from Item as s;

或者您可以使用或手动 旋转行PIVOT command（
我更喜欢后一种方法，对我来说似乎更灵活，但是pivot在某些情况下可以大大减少代码量）：

with cte as (
    select
        *,
        row_number() over(partition by sv.ItemID order by sv.SearchValueID) as row_num
    from ItemSearch as sv
)
select
    s.ItemID, s.Code, s.Name,
    max(case when sv.row_num = 1 then sv.SearchValueID end) as SearchValueID1,
    max(case when sv.row_num = 2 then sv.SearchValueID end) as SearchValueID2,
    max(case when sv.row_num = 3 then sv.SearchValueID end) as SearchValueID3,
    max(case when sv.row_num = 4 then sv.SearchValueID end) as SearchValueID4
from Item as s
    inner join cte as sv on sv.ItemID = s.ItemID
group by s.ItemID, s.Code, s.Name

您还可以将前面的语句转换为 动态SQL， 如下所示：

declare @stmt nvarchar(max)

select
    @stmt = 
        isnull(@stmt + ',','') + 
        'max(case when sv.row_num = ' + cast(rn as nvarchar(max)) +
        ' then sv.SearchValueID end) as SearchValueID' + cast(rn as nvarchar(max))
from (
    select distinct row_number() over(partition by ItemID order by SearchValueID) as rn
    from ItemSearch
) as a

select @stmt = '
    with cte as (
        select
            *,
            row_number() over(partition by sv.ItemID order by sv.SearchValueID) as row_num
        from ItemSearch as sv
    )
    select
        s.ItemID, s.Code, s.Name,' + @stmt + '
    from Item as s
        inner join cte as sv on sv.ItemID = s.ItemID
    group by s.ItemID, s.Code, s.Name;'

exec dbo.sp_executesql @stmt = @stmt

[sql fiddle demo](http://sqlfiddle.com/#!6/dee00/41)