问题描述
我认为相当简单的查询似乎比我预期的要复杂一些.
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.
示例数据
DECLARE @TABLE1 TABLE(UserID INT,Episode INT ,[Value] VARCHAR(100))
INSERT INTO @TABLE1 VALUES
(1, 1,'VALUE 1-1'),(1, 2,'VALUE 1-2')
DECLARE @TABLE2 TABLE(UserID INT,Episode INT ,[Details] VARCHAR(100))
INSERT INTO @TABLE2 VALUES
(1, 1,'Details 1'),(1, 1,'Details 2'),(1, 2,'Details 1'),(1, 2,'Details 2')
简单加入
SELECT ONE.UserID
, ONE.Episode
, ONE.Value
, TWO.Details
FROM @TABLE1 ONE INNER JOIN @TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
╔════════╦═════════╦═══════════╦═══════════╗
║ UserID ║ Episode ║ Value ║ Details ║
╠════════╬═════════╬═══════════╬═══════════╣
║ 1 ║ 1 ║ VALUE 1-1 ║ Details 1 ║
║ 1 ║ 1 ║ VALUE 1-1 ║ Details 2 ║
║ 1 ║ 2 ║ VALUE 1-2 ║ Details 1 ║
║ 1 ║ 2 ║ VALUE 1-2 ║ Details 2 ║
╚════════╩═════════╩═══════════╩═══════════╝
在这种情况下，我希望 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 查询
SELECT * FROM
(
SELECT ONE.UserID
, ONE.Episode
, ONE.Value
, TWO.Details
FROM @TABLE1 ONE INNER JOIN @TABLE2 Two
ON ONE.UserID = TWO.UserID AND ONE.Episode = TWO.Episode)Q
PIVOT (MAX(Details)
FOR Details
IN ([Details 1], [Details 2]))p
╔════════╦═════════╦═══════════╦═══════════╦═══════════╗
║ UserID ║ Episode ║ Value ║ Details 1 ║ Details 2 ║
╠════════╬═════════╬═══════════╬═══════════╬═══════════╣
║ 1 ║ 1 ║ VALUE 1-1 ║ Details 1 ║ Details 2 ║
║ 1 ║ 2 ║ VALUE 1-2 ║ Details 1 ║ Details 2 ║
╚════════╩═════════╩═══════════╩═══════════╩═══════════╝
这正是我想要的，从名为 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:
SELECT ONE.UserID,
ONE.Episode,
ONE.Value,
TWO.Details,
'Details'
+cast(row_number() over(partition by one.userid, one.episode
order by two.details) as varchar(10)) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
这将为新列名称创建一个唯一的序列，然后您可以应用 PIVOT:
This will create a unique sequence for the new columns names, then you can apply the PIVOT:
select userid, episode,
value,
details1,
details2
from
(
SELECT ONE.UserID,
ONE.Episode,
ONE.Value,
TWO.Details,
'Details'
+cast(row_number() over(partition by one.userid, one.episode
order by two.details) as varchar(10)) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
) d
pivot
(
max(details)
for seq in (Details1, Details2)
) piv;
参见SQL Fiddle with Demo.然后您可以将其转换为动态 SQL:
See SQL Fiddle with Demo. Then you can convert this to dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME('Details'+cast(seq as varchar(10)))
from
(
select
row_number() over(partition by one.userid, one.episode
order by two.details) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
) d
group by seq
order by seq
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT userid, episode, value, ' + @cols + '
from
(
SELECT ONE.UserID,
ONE.Episode,
ONE.Value,
TWO.Details,
''Details''
+cast(row_number() over(partition by one.userid, one.episode
order by two.details) as varchar(10)) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
) x
pivot
(
max(details)
for seq in (' + @cols + ')
) p '
execute sp_executesql @query;
参见SQL Fiddle with Demo.给你结果:
| USERID | EPISODE | VALUE | DETAILS1 | DETAILS2 |
|--------|---------|-----------|-----------|-----------|
| 1 | 1 | VALUE 1-1 | Details 1 | Details 2 |
| 1 | 2 | VALUE 1-2 | Details 1 | Details 2 |
这篇关于动态 PIVOT，从两个表的 JOIN 返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！