如何将行转换为列? [英] How to convert row to columns?

问题描述

我有下表:

  PROCESS ID       VOUCHER DATE     CATEGORY_ID     PROJECT     AMOUNT
  -----------      ------------    ------------     -------     -------
  1001             12/03/13         Miscellaneous   pnr         1000
  1001             12/03/13         Miscellaneous   pjk         2000
  1002             20/07/13         Local Travel    pnr         3000
  1002             20/07/13         Local Travel    npk         3400
  1003             29/09/14         Miscellaneous   jpg         1000
  1004             23/10/13         Local Travel    pnr         2000
  1005             24/10/13         Miscellaneous   pnr         1000
  1005             24/10/13         Local Travel    pnr         1000

在界面中，我将在某些日期(例如 20/01/13 和 27/10/13 )之间给出 VOUCHER DATE 我必须按照以下格式获取输出:

In the interface I will give VOUCHER DATE as between some date e.g., 20/01/13 and 27/10/13 and I have to get the output as in the format below:

  CATEGORY_ID       pnr         npk         jpg
  -----------      -----       -----       -----      
  Miscellaneous    1000         0           1000
  Local Travel     6000         3400        0

项目是动态的，可以变化.

The PROJECT is dynamic, it can vary.

推荐答案

尝试一下，它可以在SQL Server 2008中使用:

Try this, it works in SQL Server 2008:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + PROJECT
                    FROM (SELECT DISTINCT PROJECT FROM Table1 )sub
                   FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @query = 'SELECT * FROM
                (
                SELECT CATEGORY_ID, PROJECT, AMOUNT 
                FROM Table1   
                --WHERE VOUCHER_DATE BETWEEN '' AND ''
                ) AS T1
                PIVOT (SUM(AMOUNT) FOR PROJECT IN ('+@cols+')) AS T2

'
EXEC(@query)

SQL提琴

基本上，您使用动态SQL来设置项目列表，因为它可能会有所不同，然后照例使用PIVOT，我注释了凭证日期标准，因为样本日期的格式不正确.如果某些项目被日期要求完全排除在外，那么您还将日期标准添加到设置项目列表的顶部.

Basically, you use dynamic SQL to set the list of projects since it can vary, then PIVOT as per usual, I commented out the voucher date criteria because the sample dates weren't in proper format. If some projects are excluded entirely by the date requirement, then you will also add the date criteria to the top portion that sets the project list.

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