在 Sql Server 中编写 TRANSFORM 语句 [英] write a TRANSFORM statement in Sql Server

问题描述

我正在将 Web 应用程序后端从 Access 迁移到 MSSQL，但是我无法在 MSSQL 中重现以下查询，有什么想法吗?

I am migrating a web application backend from Access to MSSQL, however I was not able o reproduce the following Query in MSSQL, any ideas?

TRANSFORM First(FollowUp.FUData) AS FirstOfFUData
SELECT FollowUp.MRN
FROM FollowUp
GROUP BY FollowUp.MRN
PIVOT FollowUp.FU;

请注意，此查询将数据从 EAV 表 Followup 转换为普通表.这是表Followup的设计:

please note that this query converts data from the EAV table Followup to a normal table. This is the design of the table Followup:

推荐答案

在 SQL Server 中，您可以使用 PIVOT 函数，您的查询将以这种方式设置:

In SQL Server you can use the PIVOT function and your query would be set up this way:

select MRN, Value1, Value2
from
(
  select MRN, FUData, FU
  from FollowUp
) src
pivot
(
  max(FUData)
  for FU in (Value1, Value2)
) piv

您可以将 Value1、Value2 等替换为您现在应该是列的任何值.

Where you would replace the Value1, Value2, etc with any of the values that you items that should now be columns.

SQL Server 2008，没有 FIRST() 函数，因此您必须使用另一个聚合函数或以这样的方式查询数据，以返回 FIRST() 中每个项目的第一条记录代码>FU.

SQL Server 2008, does not have a FIRST() function so you will have to use another aggregate function or query the data in such a manner to return the the first record for each item in FU.

另一种编写方法是使用带有 CASE 语句的聚合函数:

Another way to write this is using an aggregate function with a CASE statement:

select MRN, 
  max(case when FU = 'value1' then FUData else null end) Value1,
  max(case when FU = 'value2' then FUData else null end) Value2
from FollowUp
group by MRN

如果您将已知数量的 FU 值转换为列，则上述版本将非常有效，但如果您没有，则需要使用类似于以下的动态 SQL:

The above versions will work great if you have a known number of FU values to transform into columns, but if you do not then you will need to use dynamic SQL similar to this:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FU) 
                    from FollowUp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT MRN,' + @cols + ' from 
             (
                select MRN, FUData, FU
                from FollowUp
            ) x
            pivot 
            (
                max(FUData)
                for FU in (' + @cols + ')
            ) p '

execute(@query)

这篇关于在 Sql Server 中编写 TRANSFORM 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！