代码之家 › 专栏 › 技术社区 › Elango Sengottaiyan

SQL Server:行到列交换

sql-server-2008 sql-server sql

1

Elango Sengottaiyan · 技术社区 · 7 年前

我的样本数据

Month Name    MaterialName                                Qty
August        M.Sand(Premium M sand concrete)             0.000
December      M.Sand(Premium M sand Plaster -THRIVENI)    24.950
January       M.Sand(Premium M sand concrete)             527.730
January       20MM                                        68.730
January       M.Sand(Premium M sand Plaster -THRIVENI)    333.040
January       12MM                                        41.480
January       6MM                                         135.000
January       GSB                                         78.300
January       TP SAND BAG                                 55.000
November      M.Sand(Premium M sand concrete)             45.000
October      20MM                                         30.000
September    M.Sand(Premium M sand concrete)              40.000

预期结果:

Month Name | M.Sand(Premium M sand concrete) | M.Sand(Premium M sand Plaster -THRIVENI) | 20MM      
August     |         0.000           |      0.000               | 0.000
December   |         0.000           |      24.950              | 0.000
January    |         527.730             |      0.000               | 68.730

etc....

请为上述问题提供合适的解决方案。我有一个结果,如下面的屏幕截图所示:

但我想要这样的数据:

我的疑问是:

SELECT DATENAME(MONTH, TripDate) [Month Name],
       MM.MaterialName,
       ISNULL(SUM([NetWeight]), 0) Qty
FROM [TRANS].[tblWeighBridgeEntry] WB
LEFT JOIN MAS.tblMaterialMaster MM ON MM.MaterialID = WB.RefMaterialId
WHERE TripDate IS NOT NULL
  AND TripDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M, -6, CURRENT_TIMESTAMP)), 0)
GROUP BY DATENAME(MONTH, TripDate),
         RefMaterialId,
         MaterialName

2 回复 | 直到 7 年前

1

iSR5 7 年前

你可以这样做:

SELECT 
    MonthName [Month Name]
,   SUM(CASE WHEN MaterialName = 'M.Sand(Premium M sand concrete)' THEN Qty ELSE 0 END) [M.Sand(Premium M sand concrete)]
,   SUM(CASE WHEN MaterialName = 'M.Sand(Premium M sand Plaster -THRIVENI)' THEN Qty ELSE 0 END) [M.Sand(Premium M sand Plaster-THRIVEN)]
,   SUM(CASE WHEN MaterialName = '20MM' THEN Qty ELSE 0 END) [20MM]
-- ......... etc 
FROM (
    SELECT  
        DATENAME(MONTH,TripDate) [MonthName]
    ,   MM.MaterialName
    ,   SUM([NetWeight]) Qty 
    FROM 
        [TRANS].[tblWeighBridgeEntry] WB
    LEFT JOIN MAS.tblMaterialMaster MM ON MM.MaterialID = WB.RefMaterialId
    WHERE 
        TripDate  >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M, -6, CURRENT_TIMESTAMP)), 0) 
    GROUP BY 
        DATENAME(MONTH, TripDate)
    ,   MaterialName 
) D 
GROUP BY 
    MonthName

你只需要得到材料名称的精确匹配(在我上面的例子中,我尝试复制图片中的内容,但这可能有一些错误),然后只需查找、获取、求和。您可以对所有列执行相同的操作,也可以使用Pivot执行此操作。

2

0

gotqn user3521065 7 年前

试试这个:

SELECT  DATENAME(MONTH,TripDate) [Month Name],MM.MaterialName, ISNULL(SUM([NetWeight]),0) Qty
INTO #DataSource
FROM [TRANS].[tblWeighBridgeEntry] WB
LEFT JOIN MAS.tblMaterialMaster MM ON MM.MaterialID = WB.RefMaterialId
WHERE TripDate IS NOT NULL AND  TripDate  >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M, -6, CURRENT_TIMESTAMP)), 0) 
GROUP BY DATENAME(MONTH, TripDate),RefMaterialId,MaterialName 


DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@ColumnNames NVARCHAR(MAX);

SELECT @ColumnNames = STUFF
(
    (
        SELECT DISTINCT ',[' + MaterialName + ']'
        FROM #DataSource
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,1
   ,''
);

SET @DynamicTSQLStatement = N'
select ' + @ColumnNames + '
from ##DataSource
PIVOT 
(
    MAX([Qty]) FOR [key] IN (' + @ColumnNames + ')
) PVT
FOR JSON PATH;
';


EXEC sp_executesql @DynamicTSQLStatement;