SQL Server - 另一个 SQL 'Rows to Columns'问题

Question

这是我的数据：

ID；模型 年度 
1 nbsp 思域 nbsp nbsp 2008
1 nbsp 协议 nbsp；2010
2 nbsp 野马；2011
3 nbsp Tahoe nbsp 2011

我想得到这个结果：

ID；模型1 第一年 模型2 第2年 
1 nbsp 思域 nbsp nbsp 2008年 nbsp nbsp；协议 nbsp nbsp；2010年 nbsp nbsp；
2 nbsp 野马；2011
3 nbsp Tahoe nbsp 2011

每个ID下最多可以有4辆车，而且不能超过4辆。我花了很多时间研究这个问题，但还没有找到一个完全适合我的例子的好解决方案。也许是因为我不知道如何准确地表达我的搜索。谢谢

Answer 1

您应该使用PIVOT表。它很丑陋，但它有效：

if object_id('tempdb..#RepeatingGroup') is not null drop table #RepeatingGroup
select 1 as ID, 'Civic' as Model, '2008' as [Year] into #RepeatingGroup union all
select 1, 'Accord', '2010' union all
select 2, 'Mustang', '2011' union all
select 3, 'Tahoe', '2011'
if object_id('tempdb..#tmp') is not null drop table #tmp
select
    ID,
    Model,
    Year,
    row_number() over (partition by x.ID order by x.Model) as Ordinal
into
    #tmp
from
    #RepeatingGroup x
select
    pvtMd.ID,
    pvtMd.[1] as Model1,
    pvtYr.[1] as Year1,
    pvtMd.[2] as Model2,
    pvtYr.[2] as Year2,
    pvtMd.[3] as Model3,
    pvtYr.[3] as Year3,
    pvtMd.[4] as Model4,
    pvtYr.[4] as Year4
from
    (select ID, Model, Ordinal from #tmp t) t
    pivot (
        min(Model) for Ordinal in ([1], [2], [3], [4])
    ) as pvtMd,
    (select ID, Year, Ordinal from #tmp t) t2
    pivot (
        min([Year]) for Ordinal in ([1], [2], [3], [4])
    ) as pvtYr
where
    pvtMd.ID = pvtYr.ID
order by
    1

Answer 2

在SQL中没有真正好的方法来做到这一点。您可能会尝试使用数据透视表，但每个实体都需要一个序列。

你最好的选择是在你的输出语言中按照你想要的方式安排它，这将有更好的工具来处理这类事情。

向每行添加一列，称为序列（有1、2、3和4）

SELECT
id,
Max(case when seq = 1 then model end) as model1,
max(case when seq = 1 then year end) as year1,
Max(case when seq = 2 then model end) as model2,
max(case when seq = 2 then year end) as year2,
Max(case when seq = 3 then model end) as model3,
max(case when seq = 3 then year end) as year3,
Max(case when seq = 4 then model end) as model4,
max(case when seq = 4 then year end) as year4
group by id