动态 PIVOT，从两个表的 JOIN 中返回结果
我认为是一个相当简单的查询似乎比我预期的要复杂一些.
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 |
相关文章