在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中，您可以使用

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()函数，因此您将不得不使用另一个聚合函数或查询数据以返回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屋！