创建交叉表查询 [英] Creating cross tab queries

问题描述

我有这样的桌子

Hi,

I''ve table like this

----------------------------------
Name          Date       Status
----------------------------------
Jackson      01/01/2012    P
Jackson      02/01/2012    P
Jackson      03/01/2012    A
Jackson      04/01/2012    P
Jackson      05/01/2012    H
Jackson      06/01/2012    P 

我想要这样的报告

i want a report like this

                1  2  3  4  5  6
         
JACKSON         P  P  A  P  H  P

我正在使用SQL Server 2005，并希望在Crystal Report中获取报告

我已经尝试过交叉标签查询，但无法成功

I''m Using SQL server 2005 and want get report in Crystal Report

I''ve tried Cross tab Query But Can''t succeed

SELECT *
FROM (
    SELECT
        staff_name,day(Pday) as [DAY],
        status
    FROM Temp_Must_ROll
) as s
PIVOT
(
    status FOR [DAY] IN (
        [1], [2], [3], [4], [5],[6]
    )
)AS p 

请帮帮我!!!

[edit]已删除呼喊声-OriginalGriff [/edit]

Please Help Me !!!

[edit]SHOUTING removed - OriginalGriff[/edit]

推荐答案

尝试一下

select distinct nu.name as Name,
                m1.status as '1',
                m2.status as '2',
                m3.status as '3',
                m4.status as '4',
                m5.status as '5',
                m6.status as '6'
           from test nu
left outer join test m1 on m1.date = '2012-01-01' and m1.name = nu.name
left outer join test m2 on m2.date = '2012-01-02' and m2.name = nu.name
left outer join test m3 on m3.date = '2012-01-03' and m3.name = nu.name
left outer join test m4 on m4.date = '2012-01-04' and m4.name = nu.name
left outer join test m5 on m5.date = '2012-01-05' and m5.name = nu.name
left outer join test m6 on m6.date = '2012-01-06' and m6.name = nu.name;

这篇关于创建交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！