动态PIVOT，从两个表的JOIN返回结果 [英] PIVOT dynamically, Returned results from JOIN of two tables
问题描述
我认为这是一个非常简单的查询，似乎比我预期的要棘手.
What I thought was a fairly simple query seems to be a bit more tricky then what I anticipated.
我有两个桌子.具有一对多关系.我想做的是表2返回的任何记录，我希望它位于单独的列中.我已经在下面的查询中使用相对较小的数据并知道第二张表返回了什么来做到这一点.
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 ║
╚════════╩═════════╩═══════════╩═══════════╩═══════════╝
这正是我想要的，从表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.
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小提琴.然后，您可以将其转换为动态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小提琴.给您结果:
| 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返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
