将一行转换为列 [英] convert one row into columns

问题描述

这是我的查询.这会显示屏幕截图中描述的结果.现在我想改变它，让它在列中显示当月的雕像.

This is my query. That show me results as described in the screen shot. Now i want to change it so it show me statues of the month in columns.

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

我认为这张图片可以很好地描述我的需求.

I think this image will describe well what i need.

请让我知道如何按月份名称对其进行排序...例如.一月、二月、三月、六月、十二月等

Please let me know how can i sort it by month name.. eg. Jan, feb, mar, jun, dec. etc

谢谢.

推荐答案

查看数据透视表；

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

对有限数量的 StatusTypeNames 的简单查询类似于；

A simple query for a finite number of StatusTypeNames would be something like like;

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.

Note the use of MAX. If there is chance you'll have multiple rows with the same monthname and status typename combination, then you might want to use SUM.

要使用 madhivinan 建议的动态列，您可以按照 这个例子. 滚动到底部.

To make use of dynamic columns as madhivinan suggests, you can follow this example. Scroll to the bottom.

我试图让它与你的例子一起工作，但因为我有几个问题，可能是因为我没有表格.但是，类似以下内容正是您所追求的.

I tried to get it to work with your example, but because I had a couple of issues probably due to the fact I didn't have the tables. However, something like the following is what you are after.

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)

这并不完全正确，但这是一个起点.

It's not exactly right, but it's a starting point.

祝你好运.

这篇关于将一行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！