行转列应用案例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE @Sql NVARCHAR(MAX)
DECLARE @ExecSql NVARCHAR(MAX)
--获得需要扩展的列明
SELECT @Sql = ISNULL(@Sql + '],[', '') + Name
FROM dbo.CodeProjectKind
WHERE YearNo = 2010
GROUP BY Name,Code
ORDER BY Code
SET @Sql = '[' + @Sql + ']'
SET @ExecSql='
SELECT
CompanyId,
'+@Sql+'
FROM (SELECT
A.CompanyId,
ProjectKind = C.Name ,
PlanTotalAmount =ISNULL(A.PlanTotalAmount,0)
FROM dbo.ProjectPlan A
LEFT JOIN dbo.Company B
ON A.CompanyId = B.Id
LEFT JOIN dbo.CodeProjectKind C
ON A.ProjectKindId = C.Id
WHERE A.YearNo = 2010
AND A.PhaseId=''BE55C1869CE94909B0A4A896C5CE2496''
AND A.Version = ''20100831163638140'') A
PIVOT (SUM(PlanTotalAmount)
--FOR 需要扩展的字段 IN(列名)
FOR ProjectKind IN (' + @Sql + ')) B
ORDER BY CompanyId
'
EXEC (@ExecSql)
DECLARE @ExecSql NVARCHAR(MAX)
--获得需要扩展的列明
SELECT @Sql = ISNULL(@Sql + '],[', '') + Name
FROM dbo.CodeProjectKind
WHERE YearNo = 2010
GROUP BY Name,Code
ORDER BY Code
SET @Sql = '[' + @Sql + ']'
SET @ExecSql='
SELECT
CompanyId,
'+@Sql+'
FROM (SELECT
A.CompanyId,
ProjectKind = C.Name ,
PlanTotalAmount =ISNULL(A.PlanTotalAmount,0)
FROM dbo.ProjectPlan A
LEFT JOIN dbo.Company B
ON A.CompanyId = B.Id
LEFT JOIN dbo.CodeProjectKind C
ON A.ProjectKindId = C.Id
WHERE A.YearNo = 2010
AND A.PhaseId=''BE55C1869CE94909B0A4A896C5CE2496''
AND A.Version = ''20100831163638140'') A
PIVOT (SUM(PlanTotalAmount)
--FOR 需要扩展的字段 IN(列名)
FOR ProjectKind IN (' + @Sql + ')) B
ORDER BY CompanyId
'
EXEC (@ExecSql)