Subbu G
asked on
Transposing columns in to rows in SQL Server 2008
Please look at the screenshot and help me how I can transpose Category cost types column in to 9 columns.
I want like the resultset to be
Project Number, Project Priority List date, Secondary treatment, ...Non point source, PPL Sum
I want like the resultset to be
Project Number, Project Priority List date, Secondary treatment, ...Non point source, PPL Sum
SELECT Project.ProjectNumber,
ProjectPriorityListOrderOfApprovalDate.ProjectPriorityListDate,
--Cat Cost Info section
CategoryCostType.[Type] AS CategoryCostType,
ProjectCategoryCosts.TotalAmount AS CategoryCostTypeTotal,
ProjectCategoryCostInformation.PPLCategorySumAmount AS PPLSum
FROM Project
INNER JOIN ProjectPriorityAssignmentSRFSWQIFNPS
on project.projectid= ProjectPriorityAssignmentSRFSWQIFNPS.projectid
INNER JOIN ProjectPriorityListOrderOfApprovalDate
on ProjectPriorityAssignmentSRFSWQIFNPS.ProjectPriorityListOrderOfApprovalDateId= ProjectPriorityListOrderOfApprovalDate.ProjectPriorityListOrderOfApprovalDateId
LEFT JOIN ProjectCategoryCostInformation
ON ProjectCategoryCostInformation.ProjectId = Project.ProjectId
INNER JOIN CategoryCostType
ON CategoryCostType.ProjectTypeId=Project.ProjectTypeId
LEFT JOIN ProjectCategoryCosts
ON Project.ProjectId = ProjectCategoryCosts.ProjectId AND ProjectCategoryCosts.IsOrderOfApprovalCost = 1 AND
ProjectCategoryCosts.CategoryCostTypeId = CategoryCostType.CategoryCostTypeId
WHERE Project.projectnumber in ('3002-01','5001-01','7808-01')
ORDER BY Project.ProjectNumber
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ne ver mind,
I got it.
I got it.
ASKER
Ne ver mind, I got it.
SELECT 'CategoryCostTypeTotal' AS CategoryCostType,
[0] as 'Cat1', [1], [2], [3], [4],[5], [6], [7], [8], [9]
FROM
(
SELECT CategoryCostType.[Type] AS CategoryCostType, ProjectCategoryCosts.Total Amount AS CategoryCostTypeTotal
FROM Project
INNER JOIN ProjectPriorityAssignmentS RFSWQIFNPS
on project.projectid= ProjectPriorityAssignmentS RFSWQIFNPS .projectid
INNER JOIN ProjectPriorityListOrderOf ApprovalDa te
on ProjectPriorityAssignmentS RFSWQIFNPS .ProjectPr iorityList OrderOfApp rovalDateI d= ProjectPriorityListOrderOf ApprovalDa te.Project PriorityLi stOrderOfA pprovalDat eId
LEFT JOIN ProjectCategoryCostInforma tion
ON ProjectCategoryCostInforma tion.Proje ctId = Project.ProjectId
INNER JOIN CategoryCostType
ON CategoryCostType.ProjectTy peId=Proje ct.Project TypeId
LEFT JOIN ProjectCategoryCosts
ON Project.ProjectId = ProjectCategoryCosts.Proje ctId AND ProjectCategoryCosts.IsOrd erOfApprov alCost = 1 AND
ProjectCategoryCosts.Categ oryCostTyp eId = CategoryCostType.CategoryC ostTypeId
WHERE Project.projectnumber in ('5001-01')
) AS SourceTable
PIVOT
(avg(CategoryCostTypeTotal ) FOR CategoryCostType IN ([0], [1], [2], [3], [4],[5], [6], [7], [8], [9])) AS PivotTable;
SELECT 'CategoryCostTypeTotal' AS CategoryCostType,
[0] as 'Cat1', [1], [2], [3], [4],[5], [6], [7], [8], [9]
FROM
(
SELECT CategoryCostType.[Type] AS CategoryCostType, ProjectCategoryCosts.Total
FROM Project
INNER JOIN ProjectPriorityAssignmentS
on project.projectid= ProjectPriorityAssignmentS
INNER JOIN ProjectPriorityListOrderOf
on ProjectPriorityAssignmentS
LEFT JOIN ProjectCategoryCostInforma
ON ProjectCategoryCostInforma
INNER JOIN CategoryCostType
ON CategoryCostType.ProjectTy
LEFT JOIN ProjectCategoryCosts
ON Project.ProjectId = ProjectCategoryCosts.Proje
ProjectCategoryCosts.Categ
WHERE Project.projectnumber in ('5001-01')
) AS SourceTable
PIVOT
(avg(CategoryCostTypeTotal
ASKER
Looks like I have some syntax problem, not sure how to fix it though..can you help me fix this one?
Error:Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'FOR'.
Open in new window