问题描述
我认为相当简单的查询似乎比我预期的要复杂一些.
What I thought was a fairly simple query seems to be a bit more tricky then what I anticipated.
我有两张桌子.一对多的关系.我想要做的是表二返回的任何记录我希望它在一个单独的列中.我设法在以下查询中使用相对较小的数据完成了这项工作,并且知道第二个表返回的内容.
I have two tables. With One-to-many relation. What I am trying to do is any record returned by table two I want it to be in a separate column. I have managed to do it in this following query with relative small data and knowing what is being returned by the 2nd table.
示例数据
简单加入
在这种情况下,我希望 PIVOT
详细信息列.我设法用一个非常简单的 PIVOT 查询来完成,如下
In this case I would like to PIVOT
the Details Column. Which I managed to do with a quite simple PIVOT query as follows
PIVOT 查询
这正是我想要的,从名为 Details 1
、Details 2
和 Details 3
的列中返回的所有记录等等……
This is exactly what I want , All the records returned from table two in Columns Named as Details 1
, Details 2
and Details 3
and so on...
在这种情况下,它起作用了,因为重新调整的数据是字符串,如 "Details 1" 、 "Details 2" 和 "Details 3".
In this case it worked because data retuned itself is strings as "Details 1" , "Details 2" and "Details 3".
但是当我不知道将从 table2 返回多少行以及数据是什么时,我正在努力调整它.
But when I dont know how many rows will be returned from table2 and what will be the data I am struggling to pivot that.
还有一件更重要的事情是从表 2 返回的数据是 Large Text values
由几列连接而成.
also one more important thing is that data returned from table two is Large Text values
made up of few columns concatenated.
我试图遵循 中给出的逻辑这个 , 这个 和 this 问题,但没有乐趣.
I have tried to follow logic given in this , this and this questions but no joy.
非常感谢任何指向正确方向的帮助,在此先感谢您.
Any help any pointer in the right direction is much appreciated, Thank you in advance.
推荐答案
也许我遗漏了一些东西,但您应该能够对数据进行 PIVOT 但您需要实现 row_number()
以帮助生成列.
Maybe I am missing something but you should be able to PIVOT the data but you will need to implement row_number()
to help generate the columns.
关键是使用类似于以下内容的查询:
The key will be to use a query similar to:
这将为新列名称创建一个唯一的序列,然后您可以应用 PIVOT:
This will create a unique sequence for the new columns names, then you can apply the PIVOT:
参见SQL Fiddle with Demo.然后您可以将其转换为动态 SQL:
See SQL Fiddle with Demo. Then you can convert this to dynamic SQL:
参见SQL Fiddle with Demo.给你结果:
这篇关于动态 PIVOT,从两个表的 JOIN 返回结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!