如何选择每个GROUP BY项作为单独的列？

I get something like:

我有类似的东西:

MachID  DownCode    DownTime
PR01    COMP                    30.83
PR02    DIE SET                 378.205277777778
PR02    Die Wait                88.0533333333333
PR03    ColorChg                8.54194444444444
PR04    DieStart                0.205277777777778

From the query:

从查询:

SELECT MachID, DownCode, SUM(EndTime - StartTime)/3600 AS DownTime 
FROM vReportDownLog
GROUP BY MachID, DownCode

But I really want something like:

但我真的想要这样的东西:

MachID ColorChg COMP DIE SET DieStart Die Wait
PR01   0        30.83
PR02   0        0    378.205 0        88.0533
PR03   8.5419   0    0       0        0
PR04   0        0    0       0.2052   0

It would be much easier to use the data with Google Visualization this way.

以这种方式将数据用于Google Visualization会容易得多。

Basically, I want the first GROUP BY column as the left column, and the second GROUP BY column as the column headers, with the numeric value between them, like a table would normally be.

基本上,我希望第一个GROUP BY列作为左列,第二个GROUP BY列作为列标题,它们之间具有数值,就像通常的表一样。

After a little work, I got a modified version of the dynamic SQL to work:

经过一番工作后,我得到了动态SQL的修改版本:

BEGIN TRANSACTION

DECLARE @UsePivot as VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

-- clever way of concatenating the rows of that column
-- just a little bit cleaner than using a cursor
-- and probably faster, too. Requires I think SQL 2008+
SELECT @UsePivot =
STUFF((
    SELECT DISTINCT ',[' + DownCode + ']'
    FROM DownCodes
    FOR XML PATH('')
), 1, 1, '' )

SET @sql = '
SELECT MachID, ' + @UsePivot + '  FROM
(
    SELECT MachID, DownCode, SUM(CAST(EndTime AS Float) - CAST(StartTime AS Float))/3600 AS DownTime
    FROM vReportDownLog
    GROUP BY
    MachID, DownCode
) TBL
PIVOT
(
    SUM(DownTime)
    FOR DownCode IN (' + @UsePivot + ')
)
AS Pvt'

EXEC (@sql)

ROLLBACK

2 个解决方案

#1

Use PIVOT: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

使用PIVOT:http://technet.microsoft.com/en-us/library/ms177410(v = sql.105).aspx

begin transaction

declare @UsePivot as varchar(max)
declare @sql as varchar(max)
declare @downcode as varchar(100)


DECLARE rcursor CURSOR
    FOR SELECT DownCode FROM DownCodes
OPEN rcursor
FETCH NEXT FROM rcursor
INTO @downcode

WHILE @@FETCH_STATUS = 0
BEGIN

Set @UsePivot = isnull(@UsePivot,'') + '[' + LTRIM(RTRIM(@downcode)) + '],'

FETCH NEXT FROM rcursor
INTO @downcode

END 
CLOSE rcursor;
DEALLOCATE rcursor;

---Remove last comma
Set @UsePivot = SUBSTRING(@UsePivot,1,len(@UsePivot)-1)

SET @sql = '(SELECT MachID, (EndTime - StartTime)/3600 as [DownTime], ' + @UsePivot + ' as [totals] FROM vReportDownLog) as TBL '
+ 'PIVOT (SUM(DownTime) for DownCode in (' + @UsePivot + ')) as Pvt Group by MachID'


exec @sql

rollback

I made a dynamic query to match the downcode for all ocurrences in your table.

我做了一个动态查询来匹配表中所有事件的下行代码。

#2

select * from table pivot (sum(downtime) for downcode in
(COMP,
[DIE SET],
[Die Wait],
ColorChg,
DieStart) )as pvt

#1