如何在SQL中透视表

Lab Test Name    Source Collected Date    Results
Urea             6/4/2013 12:00:00 AM           5
Uric Acid        6/4/2013 12:00:00 AM          10
Cholesterol      6/3/2013 12:00:00 AM          25

I have a datatable with above values.
I need to pivot it to following structure:

我有一个具有以上值的datatable。我需要将其转向如下结构:

                      Urea  Uric Acid   Cholesterol
6/4/2013 12:00:00 AM     5         10             -
6/3/2013 12:00:00 AM     -          -            25

2 个解决方案

#1

If you look at the answer linked by Mikael, you'll realize that you will need to build the columns for your pivot statement dynamically since the PIVOT syntax doesn't allow a subselect within the FOR clause. So essentially you need to do something like this:

如果您查看Mikael链接的答案，您就会发现，您需要动态地构建pivot语句的列，因为pivot语法不允许在for子句中进行子选择。所以本质上你需要这样做:

DECLARE
@cols AS NVARCHAR(MAX),
@y    AS INT,
@sql  AS NVARCHAR(MAX);

-- Construct the column list for the IN clause
-- e.g., [Urea],[Uric Acid],[Cholesterol]
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT (LabTestName) AS y FROM YourTable) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

You can now build your PIVOT statement as so:

您现在可以构建您的PIVOT语句如下:

set @SQL = N'

SELECT   SourceCollectedDate,'+@cols+N' 
FROM    YourTable
PIVOT (
SUM(results) FOR LabTestName IN ( '+@cols+N')
) AS PivotTable
ORDER BY SourceCollectedDate desc
'

And execute it:

和执行:

EXEC sp_executesql @sql

Which will produce:

这将会产生:

SourceCollectedDate     Urea    Uric Acid   Cholesterol
2013-06-04 00:00:00.000 5       10          NULL
2013-06-03 00:00:00.000 NULL    NULL        25

Just note that my example has YourTable as the table name. You need to replace that with your actual table name.

请注意，我的示例将您的表作为表名。您需要用实际的表名替换它。

SQL Fiddle (Based off of what Chad created)

SQL Fiddle(基于Chad创建的)

#2

Here's a solution that doesn't require pivot or dynamic SQL. The tradeoff is that you need to specify each possible Lab Test Name in your query.

这里有一个不需要pivot或动态SQL的解决方案。权衡的是，您需要在查询中指定每个可能的实验室测试名称。

SELECT [Source Collected Date],
  MAX(CASE WHEN [Lab Test Name] = 'Urea'
      THEN Results ELSE NULL END) AS Urea,
  MAX(CASE WHEN [Lab Test Name] = 'Uric Acid'
      THEN Results ELSE NULL END) AS [Uric Acid],
  MAX(CASE WHEN [Lab Test Name] = 'Cholesterol'
      THEN Results ELSE NULL END) AS Cholesterol
FROM Table1
GROUP BY [Source Collected Date]

See it working here.

看到它在这里工作。

#1