如何使用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 ?

推荐答案

使用首先使用静态枢轴.静态枢轴是当您提前知道数据变成列时.

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提琴

第二种方法是使用动态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提琴

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

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屋！