SQL Server查询多对多关系-如何查询? [英] SQL Server Query for Many to Many Relationship - how to query?

问题描述

这是我以前的问题的更新:多对多关系

This is an update to my previous question : Many to many relationship

以前的解决方案效果很好，但是现在我希望tgo可以稍微改善结果.我想将所有波长值都放在一行中.

The previous solution works fine, but now I want tgo improve the results a little bit. I´d like to have all the wavelength values in one row.

所以不是以下结果:

DateTimeID  Wavelength  SensorID
11435   1581,665    334
11435   1515,166    334
11435   1518,286    335

我想要类似的东西:

DateTimeID  Wavelength1 Wavelength2  SensorID
11435   1581,665    1515,166     334
11435   1518,286                 335

推荐答案

您可以使用以下命令将row_number()应用于记录:

You could use the following which applies a row_number() to the records:

select DateTimeID,
  [1] as Wavelength1, 
  [2] as Wavelength2,
  SensorId
from
(
  select [DateTimeID], [Wavelength], [SensorID],
    row_number() over(partition by DateTimeID, SensorId
                      order by DateTimeID) rn
  from yourtable
) src
pivot
(
  max(Wavelength)
  for rn in ([1], [2])
) piv

请参见带有演示的SQL提琴.

如果您将有未知数量的wavelength值，则可以使用动态SQL生成此值:

If you will have an unknown number of wavelength values, then you can use dynamic SQL to generate this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Wavelength'+cast(rn as varchar(50))) 
                    from
                    (
                      select row_number() over(partition by DateTimeID, SensorId
                                              order by DateTimeID) rn
                      from yourtable
                    ) src
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT DateTimeID,' + @cols + ', SensorId  from 
             (
                select [DateTimeID], [Wavelength], [SensorID],
                  ''Wavelength''+cast(row_number() over(partition by DateTimeID, SensorId
                                        order by DateTimeID) as varchar(50)) rn
                from yourtable
            ) x
            pivot 
            (
                max(Wavelength)
                for rn in (' + @cols + ')
            ) p '

execute(@query)

请参见带有演示的SQL小提琴

这篇关于SQL Server查询多对多关系-如何查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！