在 Sql Server 中编写 TRANSFORM 语句

本文介绍了在 Sql Server 中编写 TRANSFORM 语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在将 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)