sql - 将连接的行变成列

Question

SELECT  
  b_Name+' | '+Entity+' | '+cast(A_Date as varchar) as [top]  -- Colum 
  ,b_Name  -- Rows
  ,[Task_Category] -- Rows 
  ,[Fcn_Name] -- Rows
  ,[Task]  -- Rows
  ,[Task_ID] -- Rows
  ,[Owner] -- Rows
  ,[RAG_Status] -- Rows
   FROM [Task]

我正在尝试将连接的 [TOP] 制作成列标题，但将其余部分保留在行中。我还在学习 SQL，所以请给我申请专利。哈哈

  SELECT *
FROM
(
    SELECT 
b_Name+' | '+Entity+' | '+cast(A_Date as varchar) as [top]  -- Colum 
      ,b_Name  -- Rows
      ,[Task_Category] -- Rows 
      ,[Fcn_Name] -- Rows
      ,[Task]  -- Rows
      ,[Task_ID] -- Rows
      ,[Owner] -- Rows
      ,[RAG_Status] -- Rows
     FROM [IntegrationDBdev].[dbo].[Task_Acquisition]
) AS SourceTable PIVOT(AVG(task) FOR ????
IN(
??????

) ) AS PivotTable;

我已经尝试了上述方法，但不确定在枢轴上放什么

标签： sqlsql-servertsql

Answer 1

我不建议在 Pivot 中使用 top 作为串联，因为在“IN”条件下，您也必须进行串联。

但是对于您的代码，您可以拥有它

CREATE TABLE Grades(
  [Student] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Marks]   INT
)
GO
 
INSERT INTO Grades VALUES 
('Jacob','Mathematics',100),
('Jacob','Science',95),
('Jacob','Geography',90),
('Amilee','Mathematics',90),
('Amilee','Science',90),
('Amilee','Geography',100)
GO

SELECT * FROM (
  SELECT
    [Student],
    [Subject]+'|'+[Student] as [Test1],
    [Marks]
  FROM Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Test1]
  IN (
    [Mathematics|Amilee],
    [Science|Amilee],
    [Geography|Amilee],
    [Mathematics|Jacob],
    [Science|Jacob],
    [Geography|Jacob]
  )
) AS PivotTable

我会建议它像

SELECT * FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Mathematics],
    [Science],
    [Geography]
  )
) AS PivotTable

这将给出结果

让我知道是否需要更多澄清。