现在有表 jack,其结构如下;A B C
---------------------------------------------------------
20009-04-1 JAVA 6
20009-04-2 C 10
20009-04-3 WEB 8
.
.
.
.
.
.
2009-04-30 ORACLE 7
其中表中的A列是时间，并且是动态的，比如说现在是5月，那么表中A列数据是从20009－4－1到2009－4－30
，如果现在是6月，那么现在表中A列的数据是20009－5－1到2009－5－31
现在想得到如下结果,比如现在是5月份
B 2009-04-1 20009-04-2 2009-04-3 ............ 2009-04-30
-----------------------------------------------------------------------------------
JAVA 6(C列的值)
C 10
WEB 8
ORACLE 7请各位高手指教，多谢!
---------------------------------------------------------
20009-04-1 JAVA 6
20009-04-2 C 10
20009-04-3 WEB 8
.
.
.
.
.
.
2009-04-30 ORACLE 7
其中表中的A列是时间，并且是动态的，比如说现在是5月，那么表中A列数据是从20009－4－1到2009－4－30
，如果现在是6月，那么现在表中A列的数据是20009－5－1到2009－5－31
现在想得到如下结果,比如现在是5月份
B 2009-04-1 20009-04-2 2009-04-3 ............ 2009-04-30
-----------------------------------------------------------------------------------
JAVA 6(C列的值)
C 10
WEB 8
ORACLE 7请各位高手指教，多谢!
with tb as
(
select datepart(mm,addtime)as months,count(gid) as num
from guest where datepart(yy,addtime)=2009 and belongteacher=2
group by datepart(dd,guest.addtime),
datepart(yy,addtime),datepart(mm,addtime)
)
select isnull([1],0)as '1月',isnull([2],0)as '2月',
isnull([3],0)as '3月',isnull([4],0)as '4月',isnull([5],0)as '5月',isnull([6],0)as '6月',isnull([7],0)as '7月',
isnull([8],0)as '8月',isnull([9],0)as '9月',isnull([10],0)as '10月',isnull([11],0)as '11月',
isnull([12],0)as '12月'
from (select * from tb) a
pivot(max(num) for months in
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) )as b
CREATE TABLE [dbo].[TABLE1]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[state] [int] NULL
)
GO
--数据
-- Add 9 rows to [dbo].[TABLE1]
SET IDENTITY_INSERT [dbo].[TABLE1] ON
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (2, N'北京', 2)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (4, N'北京', 1)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (5, N'上海', 2)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (6, N'广州', 3)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (7, N'上海', 1)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (8, N'广州', 2)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (9, N'北京', 3)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (10, N'海南', 2)
INSERT INTO [dbo].[TABLE1] ([id], [name], [state]) VALUES (11, N'海南', 4)
SET IDENTITY_INSERT [dbo].[TABLE1] OFF--查看实验中表TABLE1的数据
SELECT * FROM TABLE1--结果如下:ID NAME STATE
2 北京 2
4 北京 1
5 上海 2
6 广州 3
7 上海 1
8 广州 2
9 北京 3
10 海南 2
11 海南 4/*行转化为列的CASE方法,name为行种*/
-- 1普通方式下
SELECT NAME,
MAX(CASE STATE WHEN 1 THEN ID ELSE NULL END)AS '1',
MAX(CASE STATE WHEN 2 THEN ID ELSE NULL END)AS '2',
MAX(CASE STATE WHEN 3 THEN ID ELSE NULL END)AS '3',
MAX(CASE STATE WHEN 4 THEN ID ELSE NULL END)AS '4'
FROM TABLE1
GROUP BY NAME
--2 2005静态方法
SELECT * FROM
(SELECT * FROM TABLE1)p PIVOT (SUM(ID) FOR STATE IN([1],[2],[3],[4]))B--动态方法
--1之普通方法DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT NAME '
SELECT @sql = @sql + ' , MAX(CASE STATE WHEN ''' + CAST(STATE AS VARCHAR(12)) + ''' then ID else NULL end) [' + CAST(STATE AS VARCHAR(12)) + ']'
FROM (SELECT DISTINCT STATE FROM TABLE1) AS a
SET @sql = @sql + ' FROM TABLE1 GROUP BY NAME'
EXEC(@sql)--2之2005
DECLARE @sql VARCHAR(8000)
SELECT @sql =ISNULL(@sql +',' , '') + '['+ CAST(STATE AS VARCHAR(12))+']' FROM TABLE1 GROUP BY STATE
exec ('SELECT * FROM (SELECT * FROM TABLE1) A PIVOT (MAX(ID) FOR STATE IN ('+@sql+')) b')
--上面代码转化的结果如下:
NAME 1 2 3 4
北京 4 2 9 NULL
广州 NULL 8 6 NULL
海南 NULL 10 NULL 11
上海 7 5 NULL NULL
/*行转化为列的CASE方法,state为行种*/
--静态SQL之写法
--1 普通方式
SELECT STATE,
MAX(CASE NAME WHEN '北京' THEN ID ELSE NULL END)AS '北京',
MAX(CASE NAME WHEN '上海' THEN ID ELSE NULL END)AS '上海',
MAX(CASE NAME WHEN '广州' THEN ID ELSE NULL END)AS '广州',
MAX(CASE NAME WHEN '海南' THEN ID ELSE NULL END)AS '海南'
FROM TABLE1 A
GROUP BY STATE
--2 2005方式 的
SELECT * FROM
(SELECT * FROM TABLE1)P PIVOT (max(ID)FOR NAME IN ([北京],[广州],[海南],[上海]))B --动态SQL
--1 普通方式
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT STATE '
SELECT @sql = @sql + ' , MAX(CASE NAME WHEN ''' + NAME + ''' then ID else NULL end) [' + NAME + ']'
FROM (SELECT DISTINCT NAME FROM TABLE1) AS a
SET @sql = @sql + ' FROM TABLE1 GROUP BY STATE'
EXEC(@sql)--2 2005方式
DECLARE @sql VARCHAR(8000)
SELECT @sql=ISNULL(@sql+',','')+'['+NAME+']'FROM TABLE1 GROUP BY NAME
EXEC('SELECT * FROM (SELECT * FROM TABLE1)A PIVOT (MAX(ID)FOR NAME IN('+@sql+'))B')--上面代码转化的结果如下结果如下:
STATE 北京 上海 广州 海南
1 4 7 NULL NULL
2 2 5 8 10
3 9 NULL 6 11
4 NULL NULL NULL NULL
逆过程之例子:
--DECLARE @sql VARCHAR(8000)
--SELECT @sql =ISNULL(@sql +',' , '') + '['+ CAST(STATE AS VARCHAR(12))+']' FROM TABLE1 GROUP BY STATE
--exec ('SELECT * FROM (SELECT * FROM TABLE1) A PIVOT (MAX(ID) FOR STATE IN ('+@sql+')) b')--创建由TABLE1转化的表TABLE1_REV,进行逆转化
CREATE TABLE TABLE1_REV(NAME VARCHAR(12),
STATE1 INT,
STATE2 INT,
STATE3 INT,
STATE4 INT
)--由TABLE1更新数据到TABLE1_REV中,注意这里会有NULL值的
BEGIN TRAN
INSERT INTO TABLE1_REV
SELECT * FROM
(SELECT * FROM TABLE1)p PIVOT (SUM(ID) FOR STATE IN([1],[2],[3],[4]))B
SELECT * FROM TABLE1_REV
ROLLBACK TRANSELECT * FROM TABLE1_REVNAME STATE1 STATE2 STATE3 STATE4
北京 4 2 9 NULL
广州 NULL 8 6 NULL
海南 NULL 10 NULL 11
上海 7 5 NULL NULL--静态方法之 普通方式--这里去除了STATE1的STATE,仅仅取1,所以ORDER BY中出现 WHEN 1THEN 1..而且过滤了ID字段为NULL的
SELECT * FROM
(
SELECT NAME , STATE =1 , ID =STATE1 FROM TABLE1_REV
UNION ALL
SELECT NAME , STATE =2 , ID =STATE2 FROM TABLE1_REV
UNION ALL
SELECT NAME , STATE =3 , ID =STATE3 FROM TABLE1_REV
UNION ALL
SELECT NAME , STATE =4 , ID =STATE4 FROM TABLE1_REV
) tWHERE ID IS NOT NULL
ORDER BY NAME, CASE STATE WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
--2005
SELECT NAME , REPLACE(STATE,'STATE','')AS STATE,ID FROM TABLE1_REV unpivot (ID for STATE in([STATE1] , [STATE2] , [STATE3],[STATE4])) t--上面代码的结果如下:NAME STATE ID北京 1 4
北京 2 2
北京 3 9
广州 2 8
广州 3 6
海南 2 10
海南 4 11
上海 1 7
上海 2 5--动态sql之 经典方式declare @sql varchar(8000)
--此部分是产生选出的字段,按照","分割出来select @sql = isnull(@sql + ' union all ' , '' ) + ' SELECT NAME , [STATE] = ' + quotename(Name , '''') + ' , [ID] = ' + quotename(Name) + ' FROM TABLE1_REV'
from syscolumns
where name! = N'NAME' and ID = object_id('TABLE1_REV')
order by colid asc
--SELECT @sql
exec(@sql + ' order by NAME ')--上面的代码的结果如下:NAME STATE ID北京 STATE1 4
北京 STATE2 2
北京 STATE3 9
北京 STATE4 NULL
广州 STATE4 NULL
广州 STATE3 6
广州 STATE2 8
广州 STATE1 NULL
海南 STATE1 NULL
海南 STATE2 10
海南 STATE3 NULL
海南 STATE4 11
上海 STATE4 NULL
上海 STATE3 NULL
上海 STATE2 5
上海 STATE1 7 ----动态sql之 2005方式
declare @sql varchar(8000)
select @sql= ISNULL(@sql+',','')+'['+name+']'
from syscolumns
where name! = N'name' and ID = object_id('TABLE1_REV') GROUP BY name
select @sql
EXEC('SELECT NAME , ID , STATE FROM TABLE1_REV unpivot (ID for STATE in('+@sql+')) t')
NAME ID STATE北京 4 STATE1
北京 2 STATE2
北京 9 STATE3
广州 8 STATE2
广州 6 STATE3
海南 10 STATE2
海南 11 STATE4
上海 7 STATE1
上海 5 STATE2........