多个sql行到单行数据

Question

我需要一个SQL查询来将多个行选择语句合并为单行。

创建一个表[PriceHistory]来存储基于日期的每日金属价格，如下所示:

现有数据库值示例

需要查询来显示[PriceHistory]。[PriceHistory]的开价列。SpotMetal=黄金作为GoldSpot和[PriceHistory]。当我们使用date()函数输入当前系统日期并使用[PriceHistory]检查它时，SpotMetal=银作为SilverSpot在单行中。SpotDate

所需输出:

所需输出

我写了一个查询如下，但它显示为单独的行:

BEGIN
DECLARE @date DATETIME
SET @date=CAST(GETDATE() AS DATE)
SELECT (CASE WHEN [OpenPrice] IS NULL THEN 0 ELSE  [OpenPrice] END) AS GoldSpot FROM [][dbo].[PriceHistory] WHERE [SpotMetal]='Gold' and [SpotDate]=@date;
SELECT (CASE WHEN [OpenPrice] IS NULL THEN 0 ELSE  [OpenPrice] END) AS SilverSpot FROM [][dbo].[PriceHistory] WHERE [SpotMetal]='Silver' and [SpotDate]=@date;
END

收到输出

请建议更正我的查询，以便我得到所需的输出

Answer 1

BEGIN 
DECLARE @date DATETIME 
SET @date = CAST('2015-01-01' AS DATE)
SELECT * FROM   
(
SELECT SpotMetal
,OpenPrice
FROM PriceHistory
WHERE spotdate = @date
) t 
PIVOT(
SUM(OpenPrice) 
FOR SpotMetal IN (
[Gold], 
[Silver])
) AS pivot_table
END

Answer 2

你可以尝试使用PIVOT，它应该工作:

BEGIN 
DECLARE @date DATETIME 
SET @date = CAST(GETDATE() AS DATE) 
SELECT * 
FROM (SELECT * FROM [dbo].[PriceHistory] WHERE [SpotDate] = @DATE) M
PIVOT (SUM([OpenPrice] FOR [SpotMetal] IN ('Gold','Silver'))
PIVOT TABLE;
END