如何在没有聚合的情况下透视多列 [英] How to pivot multiple columns without aggregation

问题描述

我使用 SqlServer，我不得不承认我不太擅长它......对于高级用户来说，这可能是一个简单的问题(我希望)

I use SqlServer and i have to admit that i'm not realy good with it ... This might be and easy question for the advanced users (I hope)

我有两张像这样的表

第一张表(ID 不是主键)

First table (ID isn't the primary key)

ID      IdCust   Ref
1       300      123
1       300      124
2       302      345

第二个(ID 不是主键)

And the second (ID isn't the primary key)

ID     Ref      Code    Price
1      123      A       10
1      123      Y       15
2      124      A       14
3      345      C       18

在第二张表中，列Ref"是第一张表中Ref"的外键

In the second table, the column "Ref" is the foreign key of "Ref" in the first table

我正在尝试生成以下输出:

I'm trying to produce the following output:

股票"、代码"和价格"列可以有x个值，所以我不知道，提前...

The column "Stock", "Code" and "Price" can have x values, so I don't know it, in advance...

我尝试了很多诸如PIVOT"之类的东西，但没有给我正确的结果，所以我希望有人能解决我的问题......

I tried so many things like "PIVOT" but it didn't give me the right result, so i hope someone can solve my problem ...

推荐答案

使用 row_number() 函数并进行条件聚合:

Use row_number() function and do the conditional aggregation :

select id, IdCust, Ref,
       max(case when Seq = 1 then stock end) as [Stock A], -- second table *id*
       max(case when Seq = 1 then code end) as [Code 1],
       max(case when Seq = 1 then price end) as [Price1],
       max(case when Seq = 2 then stock end) as [Stock B], -- second table *id*
       max(case when Seq = 2 then code end) as [Code 2],
       max(case when Seq = 2 then price end) as [Price2]
from (select f.*, s.Id Stock, s.Code, s.Price,
             row_number() over (partition by f.Ref order by s.id) as Seq
     from first f
     inner join second s on s.Ref = f.Ref 
     ) t
group by id, IdCust, Ref;

但是，this 将使用 known 值，否则您需要使用 dynamic 解决方案.

However, this would go with known values else you would need go with dynamic solution for that.

这篇关于如何在没有聚合的情况下透视多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！