将一行转换为列

Question

这是我的查询。它会像屏幕截图中描述的那样显示结果。现在我想将其更改为以列的形式显示本月的状态。

DECLARE @temp TABLE
(
MonthName           VARCHAR(10),
[Year]              VARCHAR(10),
StatusTypeId        INT,
StatusTypeName      VARCHAR(50),
StatusCount         INT
)

INSERT INTO @temp
SELECT
CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)) as MonthName,
datepart(yyyy, w.ExpectedStartDate) as [Year],
w.StatusTypeId,
st.StatusTypeName,
COUNT(ISNULL(w.StatusTypeId, 0)) AS StatusCount
FROM
Worksheet w LEFT OUTER JOIN
StatusType st ON st.StatusTypeId = w.StatusTypeId
WHERE   w.ProjectId = 20
AND CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)) between ('feb') AND    ('mar')
GROUP BY
datepart(yyyy, w.ExpectedStartDate),
CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)),
w.StatusTypeId,
st.StatusTypeName

SELECT  ISNULL(((CONVERT(VARCHAR(5), [Year])) + '-' + MonthName), 'Unknown') AS     MonthName,
    ISNULL(StatusTypeName, 'Unknown') AS StatusTypeName,
    StatusCount
FROM @temp

我认为这张图片可以很好地描述我需要的内容。

请告诉我如何按月份名称排序。例如，Jan、Feb、Mar、Jun、Dec等。

谢谢。

- Sami

Answer 1

查看数据透视表；

请参见http://msdn.microsoft.com/en-us/library/ms177410.aspx。

一个简单的查询有限数量的StatusTypeNames可能是这样的；

SELECT * FROM 
(SELECT MonthName, StatusTypeName as attributeCol, StatusCount FROM @ResultsTable) rt 
PIVOT ( MAX(StatusCount) FOR attributeCol in ([ToBeScheduled],[Complete])) as pvt
ORDER BY MonthName

注意MAX的使用。如果有可能会有多个具有相同月份名称和状态类型名称组合的行，则可能需要使用SUM。

如madhivinan所建议的那样，要利用动态列，您可以按照这个示例进行操作。滚动到底部。

我尝试使用您的示例使其正常工作，但由于我没有表格，可能出现了一些问题。但是，以下类似的内容就是您想要的。

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = SELECT STUFF (( SELECT DISTINCT '],[' + 
                    StatusTypeName FROM @ResultsTable ORDER BY '],[' + 
                    StatusTypeName FOR XML PATH ('')), 1, 2, '') + ']'


SET @query =
'SELECT * FROM
      (SELECT MonthNameCol, StatusTypeName as attributeCol, StatusCount FROM @ResultsTable) rt
PIVOT ( MAX(StatusCount) FOR attributeCol in ('+@listCol+')) AS pvt ORDER BY MonthNameCol'

EXECUTE (@query)

这不完全正确，但是可以作为一个起点。
祝好运。

- Mr Moose

---

我看过这个，但我很困惑如何将行转换为列，因为我们不知道会返回多少行以及行中的记录是什么。我的意思是动态地将行转换为列，而不知道行中的数据是什么。 - Sami

你会@萨米。我刚刚用一个简单的例子试过了，它运行正常。用我更新后的编辑再试一次。 - Mr Moose

@Moose：这是我的查询，但它给了我一个错误 Msg 207，级别16，状态1，第2行 无效的列名'MonthName'。 Msg 207，级别16，状态1，第2行 无效的列名'StatusCount'。我将在下一条评论中添加查询。我无法在此处发布它。 - Sami

DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + st.StatusTypeName FROM Worksheet w LEFT OUTER JOIN StatusType st ON st.StatusTypeId = w.StatusTypeId ORDER BY '],[' + st.StatusTypeName FOR XML PATH('') ), 1, 2, '') + ']'SET @query = 'SELECT * FROM (SELECT w.MonthName, StatusTypeName as attributeCol, StatusCount FROM Worksheet w LEFT OUTER JOIN StatusType st ON st.StatusTypeId = w.StatusTypeId ) rt PIVOT ( MAX(StatusCount) FOR attributeCol in ('+@listCol+')) AS pvt ORDER BY MonthName' EXECUTE (@query) - Sami

抱歉，这是我的错误，我给出了错误的列名。对不起。 - Sami

Answer 2

您可以使用本文中展示的动态PIVOT方法http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx。

- Madhivanan