Turn a Concatenated Row into a column

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]

I'm trying to make the concatenated [TOP] into column headings but leave the rest into rows. I'm still learning SQL so, please be patent with me. lol

  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;

I've tried the above but not sure what to put in the pivot

required reading: [Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) — Thom A, Oct 09 '20 at 13:57
At first glance the solution requires a `pivot` and dynamic SQL. Both can be challenging in SQL on their own... Any chance you can simply the output headers to something more generic like `EntityDate1`, `EntityDate2`, etc.? Also please provide some sample input data and expected result. [Pivot example](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15#basic-pivot-example) and [dynamic pivot example](https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot). — Sander, Oct 09 '20 at 14:20
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Oct 09 '20 at 14:47

score 0 · Accepted Answer · answered Oct 09 '20 at 14:23

I would not recommend this to have top as some concatenation in Pivot because in 'IN' condition you will have to have the concatenation as well.

but for your code you can have it like

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

I would recommend it to be like

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

which will give result

Let me know if any more clarificaiton needed.

IN ( 'Mathematics|Amilee', ) using sting '' in the in condition but noticed I was actually calling a column. lol — Huss1, Oct 09 '20 at 15:03

Turn a Concatenated Row into a column

1 Answers1