动态 PIVOT,从两个表的 JOIN 返回结果

2021-12-13 00:00:00 pivot sql sql-server-2008 sql-server

我认为相当简单的查询似乎比我预期的要复杂一些.

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 1Details 2Details 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 |

相关文章