--行轉列 create table T (way char(01),[date] datetime) insert into T select 'A', '2008-09-01' insert into T select 'B' , '2008-09-02' insert into T select 'C' , '2008-09-02' insert into T select 'C' , '2008-09-02' insert into T select 'C' , '2008-09-02' GOdeclare @sql varchar(8000) set @sql='' select @sql=@sql+',sum(case when convert(char(10),[date],120)+''/''+way ='''+ convert(char(10),[date],120)+'/'+way +''' then 1 else 0 end) as ['+ convert(char(10),[date],120)+'/'+way+']' from T group by [date],wayset @sql='select '+stuff(@sql,1,1,'')+' from T'exec(@sql) /* 2008-09-01/A 2008-09-02/B 2008-09-02/C ------------ ------------ ------------ 1 1 3 */drop table T
表结构如下: way datatime AA A 2008-09-01 12 B 2008-09-02 22 C 2008-09-02 11 C 2008-09-02 11 C 2008-09-02 11 结果要如下 2008-09-01/A 2008-09-02/B 2008-09-02/C AA 1 0 0 12 0 1 0 22 1 0 1 11 1 0 1 11 1 0 1 11 按日期和Way中的数据进行统计 这个怎么做
CREATE TABLE T(WAY VARCHAR(10),DATETIME VARCHAR(10)) INSERT INTO T SELECT 'A', '2008-09-01' UNION ALL SELECT 'B', '2008-09-02' UNION ALL SELECT 'C', '2008-09-02' UNION ALL SELECT 'C', '2008-09-02' UNION ALL SELECT 'C', '2008-09-02' DECLARE @S VARCHAR(8000) SET @S = 'SELECT ' SELECT @S = @S + 'SUM(CASE WHEN DATETIME= SUBSTRING('''+COL+''',1,CHARINDEX(''/'','''+COL+''')-1) AND WAY = RIGHT('''+COL+''',CHARINDEX(''/'','''+REVERSE(COL)+''')-1) THEN 1 ELSE 0 END) ['+COL+'],' FROM (SELECT DISTINCT DATETIME+'/'+WAY AS COL FROM T)T1 SET @S = SUBSTRING(@S,1,LEN(@S)-1) SET @S = @S +' FROM T 'EXEC(@S) DROP TABLE T/* 2008-09-01/A 2008-09-02/B 2008-09-02/C 1 1 3 */
CREATE TABLE T1(WAY VARCHAR(10),DATETIME VARCHAR(10),AA int) INSERT INTO T1 SELECT 'A', '2008-09-01',12 UNION ALL SELECT 'B', '2008-09-02',22 UNION ALL SELECT 'C', '2008-09-02',11 UNION ALL SELECT 'C', '2008-09-02',11 UNION ALL SELECT 'C', '2008-09-02',11 --1:静态的写法 sql2005 select way,[2008-09-01/A],[2008-09-02/B],[2008-09-02/C],AA from (select way,data=DATETIME+'/'+way,AA,'BB'=AA+0,id=row_number() over(order by way) from T1) tmp pivot ( count(BB) for data in([2008-09-01/A], [2008-09-02/B], [2008-09-02/C]) ) p--2:动态的 declare @str varchar(500) select @str= isnull(@str+',','')+ ' ['+data+']' from ( select distinct data=([datetime]+'/'+way) from T) tmp declare @strsql varchar(500) set @strsql='select way,'+@str+',AA from (select way,data=DATETIME+''/''+way,AA,''BB''=AA+0,id=row_number() over(order by way) from T1) tmp pivot ( count(BB) for data in('+@str+') )p'exec(@strsql)/* way 2008-09-01/A 2008-09-02/B 2008-09-02/C AA ---------- ------------ ------------ ------------ ----------- A 1 0 0 12 B 0 1 0 22 C 0 0 1 11 C 0 0 1 11 C 0 0 1 11(5 行受影响) */
--行轉列
create table T (way char(01),[date] datetime)
insert into T select 'A', '2008-09-01'
insert into T select 'B' , '2008-09-02'
insert into T select 'C' , '2008-09-02'
insert into T select 'C' , '2008-09-02'
insert into T select 'C' , '2008-09-02'
GOdeclare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when convert(char(10),[date],120)+''/''+way ='''+ convert(char(10),[date],120)+'/'+way
+''' then 1 else 0 end) as ['+ convert(char(10),[date],120)+'/'+way+']'
from T
group by [date],wayset @sql='select '+stuff(@sql,1,1,'')+' from T'exec(@sql)
/*
2008-09-01/A 2008-09-02/B 2008-09-02/C
------------ ------------ ------------
1 1 3
*/drop table T
表结构如下:
way datatime AA
A 2008-09-01 12
B 2008-09-02 22
C 2008-09-02 11
C 2008-09-02 11
C 2008-09-02 11
结果要如下 2008-09-01/A 2008-09-02/B 2008-09-02/C AA
1 0 0 12
0 1 0 22
1 0 1 11
1 0 1 11
1 0 1 11
按日期和Way中的数据进行统计
这个怎么做
结果要如下2008-09-01/A 2008-09-02/B 2008-09-02/C AA
1 0 0 12
0 1 0 22
0 0 1 11
0 0 1 11
0 0 1 11
CREATE TABLE T(WAY VARCHAR(10),DATETIME VARCHAR(10))
INSERT INTO T
SELECT 'A', '2008-09-01' UNION ALL
SELECT 'B', '2008-09-02' UNION ALL
SELECT 'C', '2008-09-02' UNION ALL
SELECT 'C', '2008-09-02' UNION ALL
SELECT 'C', '2008-09-02' DECLARE @S VARCHAR(8000)
SET @S = 'SELECT '
SELECT @S = @S
+ 'SUM(CASE WHEN DATETIME= SUBSTRING('''+COL+''',1,CHARINDEX(''/'','''+COL+''')-1) AND WAY = RIGHT('''+COL+''',CHARINDEX(''/'','''+REVERSE(COL)+''')-1)
THEN 1 ELSE 0 END) ['+COL+'],'
FROM
(SELECT DISTINCT DATETIME+'/'+WAY AS COL FROM T)T1
SET @S = SUBSTRING(@S,1,LEN(@S)-1)
SET @S = @S +' FROM T 'EXEC(@S)
DROP TABLE T/*
2008-09-01/A 2008-09-02/B 2008-09-02/C
1 1 3
*/
INSERT INTO T1
SELECT 'A', '2008-09-01',12 UNION ALL
SELECT 'B', '2008-09-02',22 UNION ALL
SELECT 'C', '2008-09-02',11 UNION ALL
SELECT 'C', '2008-09-02',11 UNION ALL
SELECT 'C', '2008-09-02',11 --1:静态的写法 sql2005
select way,[2008-09-01/A],[2008-09-02/B],[2008-09-02/C],AA from
(select way,data=DATETIME+'/'+way,AA,'BB'=AA+0,id=row_number() over(order by way) from T1) tmp
pivot
(
count(BB) for data in([2008-09-01/A], [2008-09-02/B], [2008-09-02/C])
) p--2:动态的
declare @str varchar(500)
select @str= isnull(@str+',','')+ ' ['+data+']' from
( select distinct data=([datetime]+'/'+way) from T) tmp
declare @strsql varchar(500)
set @strsql='select way,'+@str+',AA from
(select way,data=DATETIME+''/''+way,AA,''BB''=AA+0,id=row_number() over(order by way) from T1) tmp
pivot
(
count(BB) for data in('+@str+')
)p'exec(@strsql)/*
way 2008-09-01/A 2008-09-02/B 2008-09-02/C AA
---------- ------------ ------------ ------------ -----------
A 1 0 0 12
B 0 1 0 22
C 0 0 1 11
C 0 0 1 11
C 0 0 1 11(5 行受影响)
*/