34,575
社区成员
发帖
与我相关
我的任务
分享
--建表
create table table1(col1 varchar(50),col2 varchar(50))
insert into table1
select 'A1','2010xx' union all
select 'A2','2010cx' union all
select 'A1','2010ddd' union all
select 'A2','2011dfsd' union all
select 'A3','2011rtrr'
--select * from table1
--处理表
select col1,LEFT(col2,4) col2,COUNT(*) as con into table2
from table1
group by col1,LEFT(col2,4)
--select * from table2
--横转纵查询处理
declare @sql varchar(500)
set @sql='select col1'
select @sql=@sql+',max(case col2 when '''+ col2+''' then con else 0 end) ['+col2+']'
from (select distinct col2 from table2) a
set @sql=@sql + ' from table2 group by col1'
exec(@sql)
drop table table1
drop table table2
(5 行受影响)
(4 行受影响)
col1 2010 2011
-------------------------------------------------- ----------- -----------
A1 2 0
A2 1 1
A3 0 1
(3 行受影响)
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(col1 varchar(5),col2 varchar(10))
insert into tb
select 'A1','2010xx' union all
select 'A2','2010cx' union all
select 'A1','2010ddd' union all
select 'A2','2011dfsd' union all
select 'A3','2011rtrr'
declare @sql nvarchar(3000);
set @sql = '';
select @sql =
@sql + ',count(case when left(col2,4)=''' + left(col2,4) +
''' then 1 else null end) as ['+left(col2,4)+ ']'
from tb
group by left(col2,4)
select @sql = 'select col1 as 项目'+@sql +
' from tb group by col1'
--输出语句
select @sql
exec(@sql)
/*
项目 2010 2011
A1 2 0
A2 1 1
A3 0 1
*/
CREATE TABLE #Temp
(
Col1 CHAR(2)
,Col2 VARCHAR(20)
)
INSERT INTO #Temp
SELECT 'A1', '2010xx' UNION ALL
SELECT 'A2', '2010cx' UNION ALL
SELECT 'A1', '2010ddd' UNION ALL
SELECT 'A2', '2011dfsd' UNION ALL
SELECT 'A3', '2011rtrr'
DECLARE @Sql VARCHAR(MAX),@Sql1 VARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+'['+LEFT(Col2,4)+']' FROM #Temp GROUP BY LEFT(Col2,4)
SELECT @Sql1=ISNULL(@Sql1+',','')+'ISNULL('+'['+LEFT(Col2,4)+']'+',0) AS '+'['+LEFT(Col2,4)+']' FROM #Temp GROUP BY LEFT(Col2,4)
SELECT @Sql='SELECT Col1,'+@Sql1+'
FROM (
SELECT Col1
,LEFT(Col2,4) AS Years
,COUNT(*) Cnt
FROM #Temp
GROUP BY Col1
,LEFT(Col2,4) ) AS A
PIVOT (Max(Cnt) FOR Years IN('+@Sql+')) p'
EXEC(@Sql)
DROP TABLE #Temp
/*
Col1 2010 2011
A1 2 0
A2 1 1
A3 0 1
*/
--还有一个方法:动态sql语句
create table #tb(col1 varchar(5),col2 varchar(10))
insert into #tb
select 'A1','2010xx'
union all select 'A2','2010cx'
union all select 'A1','2010ddd'
union all select 'A2','2011dfsd'
union all select 'A3','2011rtrr'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(col2)+']=sum(case col2 when '''+rtrim(col2)+''' then 1 else 0 end)'
from (select col1,col2=LEFT(col2,4) from #tb)t group by col2
exec('select col1'+@sql+'from (select col1,col2=LEFT(col2,4) from #tb)t group by col1' )
/*
col1 2010 2011
------------------------
A1 2 0
A2 1 1
A3 0 1
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-30 13:50:03
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([col1] varchar(2),[col2] varchar(8))
insert [huang]
select 'A1','2010xx' union all
select 'A2','2010cx' union all
select 'A1','2010ddd' union all
select 'A2','2011dfsd' union all
select 'A3','2011rtrr'
--------------开始查询--------------------------
select col1,sum(CASE WHEN LEFT(col2,4)='2010' THEN 1 ELSE 0 END ) [2010],sum(CASE WHEN LEFT(col2,4)='2011' THEN 1 ELSE 0 END ) [2011]
from [huang]
GROUP BY col1
----------------结果----------------------------
/*
col1 2010 2011
---- ----------- -----------
A1 2 0
A2 1 1
A3 0 1
*/
if OBJECT_ID('tb') is not null drop table tb
create table tb(col1 varchar(5),col2 varchar(10))
insert into tb
select 'A1','2010xx' union all
select 'A2','2010cx' union all
select 'A1','2010ddd' union all
select 'A2','2011dfsd' union all
select 'A3','2011rtrr'
--查询语句
select col1 as '项目'
,SUM(case when LEFT(col2,4)='2010' then 1 else 0 end) as '2010'
,SUM(case when LEFT(col2,4)='2011' then 1 else 0 end) as '2011'
from tb
group by col1
--结果
项目 2010 2011
----- ----------- -----------
A1 2 0
A2 1 1
A3 0 1
(3 行受影响)
drop table tb
;with cte(col1,col2) as
(
select 'A1','2010xx'
union all select 'A2','2010cx'
union all select 'A1','2010ddd'
union all select 'A2','2011dfsd'
union all select 'A3','2011rtrr'
)
select col1,[2010]=sum(case when col2='2010' then 1 else 0 end)
,[2011]=sum(case when col2='2011' then 1 else 0 end)
from (select col1,col2=LEFT(col2,4) from cte)t
group by col1
/*
col1 2010 2011
A1 2 0
A2 1 1
A3 0 1
*/
if OBJECT_ID('tb') is not null drop table tb
create table tb(col1 varchar(5),col2 varchar(10))
insert into tb
select 'A1','2010xx' union all
select 'A2','2010cx' union all
select 'A1','2010ddd' union all
select 'A2','2011dfsd' union all
select 'A3','2011rtrr'
--查询语句
;with cte as(
select col1,LEFT(col2,4) col2,COUNT(*) as sm
from tb
group by col1,LEFT(col2,4)
)
select col1 as '项目'
,MAX(case when col2='2010' then sm else 0 end) as '2010'
,MAX(case when col2='2011' then sm else 0 end) as '2011'
from cte group by col1
--结果
项目 2010 2011
----- ----------- -----------
A1 2 0
A2 1 1
A3 0 1
(3 行受影响)
drop table tb