具有三个交叉表和多个列的SQL Pivot表动态 [英] Sql Pivot table with three cross tab and multiple columns dynamically

问题描述

我有下表，其值为

CREATE TABLE stud 
  ( 
     sname NVARCHAR(10), 
     hr    NVARCHAR(30), 
     dt    DATETIME, 
     att   VARCHAR(3) 
  )

INSERT INTO stud VALUES ('Abi',  '1',  '21/01/2013','a')
INSERT INTO stud VALUES ('Abi',  '2',  '21/01/2013','p')
INSERT INTO stud VALUES ('bala',  '1',  '21/01/2013','p')
INSERT INTO stud VALUES ('bala',  '2',  '21/01/2013','a')
INSERT INTO stud VALUES ('bala',  '1',  '22/01/2013','od')
INSERT INTO stud VALUES ('bala',  '2',  '22/01/2013','ml')
INSERT INTO stud VALUES ('Abi',  '1',  '22/01/2013','ml')
INSERT INTO stud VALUES ('Abi',  '2',  '22/01/2013','od')

如果选择此表，则输出为

If i select this table i get the output as

SELECT * 
FROM   stud 

sname   hr            dt                att
Abi 1   2013-01-21 00:00:00.000  a
Abi 2   2013-01-21 00:00:00.000  p
bala    1   2013-01-21 00:00:00.000  p
bala    2   2013-01-21 00:00:00.000  a
bala    1   2013-01-22 00:00:00.000  od
bala    2   2013-01-22 00:00:00.000  ml
Abi 1   2013-01-22 00:00:00.000  ml
Abi 2   2013-01-22 00:00:00.000  od

但是我希望在ASP.NET的Crystal报表中输出如下(注意:日期应动态地指定为 from_date 到 to_date )

but I want the output as follows in crystal report in ASP.NET ( Note : the date should given as dynamically as from_date to to to_date )

sname 21/01/2013 22/01/2013

sname 21/01/2013 22/01/2013

我尝试了很长一段时间的输出，但是没有输出，很紧急，请帮助我

I tried for this output from long days itself, but did't get output and it is urgent please help me

推荐答案

如果使用的是SQL Server 2005+，则可以使用几种方法来应用PIVOT函数.

If you are using SQL Server 2005+, then there are several ways that you can apply the PIVOT function.

您可以采用静态数据透视表的形式对值进行硬编码:

You can hard-code the values in the form of a static pivot:

select *
from
(
  select sname, 
    'hour_no_'+hr+'_'+convert(nvarchar(10), dt, 120) dt,
    att
  from stud
) st
pivot
(
  max(att)
  for dt in ([hour_no_1_2013-01-21], [hour_no_2_2013-01-21],
             [hour_no_1_2013-01-22], [hour_no_2_2013-01-22])
) piv

请参见带演示的SQL提琴

或者您可以在运行时使用动态sql生成sql语句.查询的动态版本为:

Or you can use dynamic sql to generate the sql statement at run-time. The dynamic version of the query is:

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

select @cols 
  = STUFF((SELECT ', ' + QUOTENAME('Hour_No_'+hr+'_'++convert(nvarchar(10), dt, 120)) 
           from stud
           group by hr, dt
           order by dt, hr
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT sname,' + @cols + ' from 
             (
                select sname, 
                  ''hour_no_''+hr+''_''+convert(nvarchar(10), dt, 120) dt,
                  att
                from stud
            ) x
            pivot 
            (
                max(att)
                for dt in (' + @cols + ')
            ) p '

execute(@query)

请参见带演示的SQL小提琴.

两者都给出结果:

| SNAME | HOUR_NO_1_2013-01-21 | HOUR_NO_2_2013-01-21 | HOUR_NO_1_2013-01-22 | HOUR_NO_2_2013-01-22 |
-----------------------------------------------------------------------------------------------------
|   Abi |                    a |                    p |                   ml |                   od |
|  bala |                    p |                    a |                   od |                   ml |

这篇关于具有三个交叉表和多个列的SQL Pivot表动态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！