I have a need to alter a view so that it displays the result set as data in rows. i.e.
I am getting the data from existing tables. The query works as designed, but the data is being used to create a pie chart. The problem is the returned data is in columns and I need it in rows to fill the chart's x,y axis'. I thought it may be possible to create a column from a variable that would display the current view in rows, but I am not even close it seems. Below is my current hack job that has all kinds of syntax errors.
Help is appreciated.
ALTER VIEW [dbo].[vw_sj_TotalSingleOrPartneredFamily]
SET FamilyTypes = (SUM(CASE WHEN P.OtherParentID > 0 THEN 1 ELSE 0 END) AS PartneredFamily, SUM(CASE WHEN P.OtherParentID IS NULL OR
p.OtherParentID = 0 THEN 1 ELSE 0 END) AS SingleFamily), ISNULL(COUNT(p.LastName), 0)[TotalFamilies]
FROM dbo.sj_Players AS p INNER JOIN
dbo.sj_ParentGuardian AS pg ON p.ParentID = pg.ParentID LEFT OUTER JOIN
dbo.sj_ParentGuardian AS opg ON p.OtherParentID = opg.ParentID