COLDFUSION: как сгруппировать по первому столбцу и преобразовать второй столбец в три отдельных столбца

SQL Server 2005+

Другим вариантом является использование PIVOT SQL Server оператор

Сначала используйте ROW_NUMBER(), чтобы ранжировать записи по сотруднику и дате. (Примечание. Если ваша таблица не содержит фактического столбца даты и времени, вы можете заменить столбец идентификаторов или преобразовать «месяц» в datetime, используя convert()).

    SELECT  employeeID
            , employeeRating
            , ROW_NUMBER() OVER ( 
                    PARTITION BY employeeID 
                    ORDER BY employeeID, theRatingDateCol DESC
            ) AS Row
    FROM   yourTable
    ...

Результаты:

employeeID  employeeRating Row
----------- -------------- --------------------
1128        30             1
1128        60             2
1128        90             3
444981      27             1
444981      77             2
444981      97             3
444981      37             4
7310000     95             1
7310000     85             2
7310000     75             3
7310000     55             4

Затем PIVOT результаты первых трех (3) строк:

    ... 
    PIVOT
    (       
            MIN(employeeRating)
            FOR Row IN ( [1],[2],[3]) 
    )

Полный запрос:

SELECT pvt.employeeID
        , pvt.[1] AS LastRating  
        , pvt.[2] AS SecondLastRating  
        , pvt.[3] AS ThirdLastRating  
FROM (
        --- order by employee and rating date (descending)
        SELECT  employeeID
                , employeeRating
                , ROW_NUMBER() OVER ( 
                    PARTITION BY employeeID 
                    ORDER BY employeeID, theRatingDateCol DESC
                ) AS Row
        FROM   yourTable
    ) data
    PIVOT
    (   -- take top 3 values
        MIN(employeeRating)
        FOR Row IN ( [1],[2],[3]) 
    ) pvt

Результаты:

employeeID  LastRating  SecondLastRating ThirdLastRating
----------- ----------- ---------------- ---------------
1128        30          60               90
444981      27          77               97
7310000     95          85               75

SQL Server 2000

К сожалению, SQL Server 2000 и более ранние версии не поддерживают ни одну из этих функций. Хотя это не так гладко, как PIVOT, вы все же можете смоделировать его с помощью подзапроса и CASE.

Во-первых, используйте подзапрос вместо ROW_NUMBER(). По сути, вы count количество записей с более ранней датой рейтинга и используете его вместо номера строки. Примечание. Предполагается, что даты оценки уникальны для каждого сотрудника. Если это не так, вам нужно будет добавить еще один столбец, чтобы разорвать связь.

Затем используйте CASE, чтобы проверить номера строк и создать столбцы для первого три записи:

SELECT  r.employeeID
        , MAX( CASE WHEN r.Row = 0 THEN r.EmployeeRating ELSE 0 END ) AS LastRating  
        , MAX( CASE WHEN r.Row = 1 THEN r.EmployeeRating ELSE 0 END ) AS SecondLastRating  
        , MAX( CASE WHEN r.Row = 2 THEN r.EmployeeRating ELSE 0 END ) AS ThirdLastRating  
FROM  (
        SELECT  m.employeeID
                , m.employeeRating
                , m.theRatingDate
                , (  SELECT COUNT(*)
                     FROM   yourTable cnt
                    WHERE  cnt.employeeID = m.employeeID
                    AND    cnt.theRatingDate > m.theRatingDate
                ) AS Row
        FROM   yourTable m
        GROUP BY m.employeeID
            , m.employeeRating
            , m.theRatingDate
        ) r
WHERE  r.Row <= 2
GROUP BY r.employeeID

Холодный синтез

Последний вариант — использовать ColdFusion. Вы можете адаптировать ответ Джеймса Молера для заполнения отдельного "основного" запроса. Перед циклом запроса создайте новый объект запроса и последовательно назовите столбцы рейтинга, например rating1,rating2,rating3. Внутри внешнего цикла добавьте строку для каждого сотрудника. Наконец, используйте счетчик для заполнения первых трех столбцов внутри внутреннего цикла.

Примечание. Исходный запрос должен быть заказан employeeID, shortDate DESC, иначе код не будет работать правильно.

<cfset newQuery = queryNew("employeeID,rating1,rating2,rating3", "integer,Decimal,Decimal,Decimal")>

<cfoutput query="originalQuery" group="employeeID">
    <!--- add new employee row --->
    <cfset ratingRow = queryAddRow(newQuery, 1)>
    <cfset newQuery["employeeID"][ratingRow] = employeeID>

    <!--- initialize rating counter --->
    <cfset ratingIndex = 0>
    <cfoutput>
        <cfset ratingColumn++>
        <!--- populate top 3 rating columns --->
        <cfif ratingColumn lte 3>
            <cfset newQuery["rating"& ratingColumn][ratingRow] = employeeRating>
        </cfif>
    </cfoutput>
</cfoutput>

Вот решение только для ColdFusion

<table>
<tr>
    <td>SupplierID</td>
    <td>LastRating</td>
    <td>SecondLastRating</td>
    <td>ThirdLastRating</td>
</tr>
<cfoutput name="qrySupplier" group="employeeID">
<cfset Rating  = 0>
<tr>
    <td>#employeeid#</td>
    <cfoutput>
       <cfset Rating++>
       <cfif Rating LTE 3>
           <td>#employeerating#</td>
       </cfif>
     </cfoutput>
</tr>
</cfoutput>
</table>

Вот что вы можете попробовать, чтобы начать. Я оставил комментарии. У меня не было доступа к созданию быстрой таблицы, поэтому она не проверена, но может быть хорошим стартером. Я учел тот факт, что у вашего сотрудника/поставщика может быть больше или меньше 3 оценок.

<!--- Counter to count ratings  --->
<Cfset x=0>
<table width="600" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>supplierid</td>
    <td>last rating</td>
    <td>second last rating</td>
    <td>thirdlastrating</td>
  </tr>
<!--- Group by employee --->
<cfoutput query="yourQuery" group="employeeid">
    <!--- if previous employee had less then 3 ratings, close off table --->
    <cfif x gt 0 and x lt 3>
         <cfif x eq 1><td>&nbsp</td><td>&nbsp;</td></tr></cfif>
         <cfif x eq 2><td>&nbsp;</td></tr></cfif>
    </cfif>
    <!--- Loop through employee --->
  <tr>
    <td>#employeeid#</td>
    <!--- Check counter to make sure we are only doing 3 ratings per line --->
    <cfif x lt 3>
        <cfoutput>
        <td>#employeerating#</td>
        <cfset x=x+1>
        </cfoutput>
    </cfif>
    <!--- If at the 3rd rating, close off the row --->
    <cfif x eq 3>
        </tr>
        <cfset x=0>
        <!--- if at 3rd rating, reset counter --->
    </cfif>
</cfoutput>
</table>