我想生成像截图一样的 SQL 查询输出 [英] I want to generate SQL query output like screenshot

问题描述

我使用 select VendorShortName,BasePrice,convert(varchar, ModifiedDate,101) as date from price where barcode='8712566383849'

现在要生成图表，我需要如下截图所示的数据.

Now for generating the graph, I need data like below screenshot.

有人可以帮我吗?

推荐答案

您可以使用 PIVOT 轻松完成此操作.这是您将如何获得它的示例.Firt Image 将向您展示简单的 PIVOT 查询以及如何实现.

You can easily do this by using PIVOT. Here is example how you will get that. Firt Image will show you simple PIVOT Query and how you achieve that.

第二张图片将向您展示动态查询生成 - 这可能会更有帮助，因为根据您的图片，您似乎需要生成动态列所以请参考第二张图片.

Second Image Will show you dynamic Query generation - which might be more helpful as based on your image it seems you require to generate dynamic columns so please refer second image for the same.

根据我在下面创建的查询片段 - 看看它是否对您有帮助.

Based on query i have created below snippet - see if it helps you.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
   + QUOTENAME(vendorshortname)
FROM (select distinct vendorshortname from Prices) AS Prices

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT ModifiedDate, ' + @ColumnName + '
FROM (select VendorShortName,BasePrice,ModifiedDate from prices where barcode=''8712566383849'') As SourceTable 
PIVOT
 (avg(BasePrice) 
      FOR VendorShortName IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

这篇关于我想生成像截图一样的 SQL 查询输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！