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

Thanks.

回答1:

Check out pivot tables;

See http://msdn.microsoft.com/en-us/library/ms177410.aspx

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

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.

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.

Good luck.

回答2:

You can make use of dynamic PIVOT approach shown in this post http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx