如何使用 SQL Server 2005 从一个表中透视数据 [英] How to Pivot data from one table with SQL server 2005

问题描述

我想从具有以下列的单个表创建查询.

I would like to create a query from a single table with the following columns.

SEQNO 是唯一键

Name   ID   Amount   Date          JOBID       SEQNO
Mark    9    200     1/2/09         1001         1
Peter   3    300     1/2/09         1001         2
Steve   1    200     2/2/09         1001         3
Mark    9    200     3/2/09         1001         4
Peter   3    300     4/2/09         1001         5
Steve   1    200     5/2/09         1001         6
Hally   1    200     5/2/09         1002         7

查询应按SUBJOBID和日期范围以这种格式输出:-

The query should output in this format by SUBJOBID and a date range:-

**NAME      ID      1/2       2/2     3/2     4/2     5/2      JOBID**<br>
Mark        9       200       NULL    200     NULL    NULL     1001   
Peter       3       300       NULL    NULL    300     NULL     1001   
Steve       1       NULL      200     NULL    NULL    200      1001   

我一直在为此进行数据透视查询.但我似乎无处可去.有人可以帮忙吗?

I have been going over pivot queries for this. But I don't seem to get anywhere. Could some one help ?

推荐答案

这实际上可以通过 PIVOT 函数.由于另一个答案没有显示与如何执行它相关的代码，这里有两种方法来 PIVOT 数据.

This actually can be done pretty easily with a PIVOT function. Since the other answer doesn't show the code associated with how to perform it, here are two ways to PIVOT the data.

首先是使用静态枢轴.静态数据透视是指您提前知道将数据转换为列的时间.

First is with a Static Pivot. A static pivot is when you know the data ahead of time to turn into columns.

select *
from 
(
    select name, id, convert(char(5), dt, 101) dt, jobid, amount
    from test
) x
pivot
(
    sum(amount)
    for dt in ([01/02], [02/02], [03/02], [04/05], [05/05])
)p
order by jobid, name

参见 SQL Fiddle with Demo

第二种方法是使用Dynamic PIVOT 在运行时识别要转换为列的值.

The second way is by using a Dynamic PIVOT to identify at run-time the values to turn to columns.

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(5), dt, 101)) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT name, id, ' + @cols + ', jobid from 
             (
                select  name, id, convert(char(5), dt, 101) dt, jobid, amount
                from test
            ) x
            pivot 
            (
                sum(amount)
                for dt in (' + @cols + ')
            ) p 
            order by jobid, name'

execute(@query)

参见 SQL Fiddle with Demo

两者都会产生相同的结果.当您事先不知道要转换为列的值时，动态效果很好.

Both will produce the same results. The Dynamic works great when you do not know the values ahead of time to convert to columns.

这篇关于如何使用 SQL Server 2005 从一个表中透视数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！