create table lala (xsmd varchar(15), bmbm int, kdsj varchar(12), storemoney int )insert into lala select '5号停机坪',231,'2013-06-26',2522 union all select '5号停机坪',231,'2013-06-27',2595 union all select '万达',220,'2013-06-26',4047 union all select '万达',220,'2013-06-27',1994 declare @tsql varchar(6000)select @tsql=isnull(@tsql,'')+',['+xsmd+']' from (select distinct xsmd from lala) tselect @tsql='select kdsj '+@tsql +' from (select xsmd,kdsj,storemoney from lala) a ' +' pivot(max(storemoney) for xsmd in('+stuff(@tsql,1,1,'')+')) p 'exec(@tsql) /* kdsj 5号停机坪 万达 ------------ ----------- ----------- 2013-06-26 2522 4047 2013-06-27 2595 1994(2 row(s) affected) */
insert into lala select '5号停机坪',231,'2013-06-26',2522 union all select '5号停机坪',231,'2013-06-27',2595 union all select '万达',220,'2013-06-26',4047 union all select '万达',220,'2013-06-27',1994 union all select '店名1',220,'2013-06-26',101 union all --> 新增 店名1 店名2 select '店名1',220,'2013-06-27',102 union all select '店名2',220,'2013-06-26',201 union all select '店名2',220,'2013-06-27',202
-- SQL没变 declare @tsql varchar(6000)
select @tsql=isnull(@tsql,'')+',['+xsmd+']' from (select distinct xsmd from lala) t
select @tsql='select kdsj '+@tsql +' from (select xsmd,kdsj,storemoney from lala) a ' +' pivot(max(storemoney) for xsmd in('+stuff(@tsql,1,1,'')+')) p '
你这里的nsert into lala select '5号停机坪',231,'2013-06-26',2522 union all select '5号停机坪',231,'2013-06-27',2595 union all select '万达',220,'2013-06-26',4047 union all select '万达',220,'2013-06-27',1994 union all 的店名是手动添加上去的,能不能是表里有的店名都自动显示上去。
你这里的nsert into lala select '5号停机坪',231,'2013-06-26',2522 union all select '5号停机坪',231,'2013-06-27',2595 union all select '万达',220,'2013-06-26',4047 union all select '万达',220,'2013-06-27',1994 union all 的店名是手动添加上去的,能不能是表里有的店名都自动显示上去。 是动态加,不是手动加的。
你这里的nsert into lala select '5号停机坪',231,'2013-06-26',2522 union all select '5号停机坪',231,'2013-06-27',2595 union all select '万达',220,'2013-06-26',4047 union all select '万达',220,'2013-06-27',1994 union all 的店名是手动添加上去的,能不能是表里有的店名都自动显示上去。 是动态加,不是手动加的。在吗?你这是存储过程的诶,我想要的不是存储过程。我要sql语句就可以了。
http://blog.csdn.net/hdhai9451/article/details/5026933
create table lala
(xsmd varchar(15),
bmbm int,
kdsj varchar(12),
storemoney int
)insert into lala
select '5号停机坪',231,'2013-06-26',2522 union all
select '5号停机坪',231,'2013-06-27',2595 union all
select '万达',220,'2013-06-26',4047 union all
select '万达',220,'2013-06-27',1994
declare @tsql varchar(6000)select @tsql=isnull(@tsql,'')+',['+xsmd+']'
from (select distinct xsmd from lala) tselect @tsql='select kdsj '+@tsql
+' from (select xsmd,kdsj,storemoney from lala) a '
+' pivot(max(storemoney) for xsmd in('+stuff(@tsql,1,1,'')+')) p 'exec(@tsql) /*
kdsj 5号停机坪 万达
------------ ----------- -----------
2013-06-26 2522 4047
2013-06-27 2595 1994(2 row(s) affected)
*/
(xsmd varchar(15),
bmbm int,
kdsj varchar(12),
storemoney int
)
insert into lala
select '5号停机坪',231,'2013-06-26',2522 union all
select '5号停机坪',231,'2013-06-27',2595 union all
select '万达',220,'2013-06-26',4047 union all
select '万达',220,'2013-06-27',1994 union all
select '店名1',220,'2013-06-26',101 union all --> 新增 店名1 店名2
select '店名1',220,'2013-06-27',102 union all
select '店名2',220,'2013-06-26',201 union all
select '店名2',220,'2013-06-27',202
-- SQL没变
declare @tsql varchar(6000)
select @tsql=isnull(@tsql,'')+',['+xsmd+']'
from (select distinct xsmd from lala) t
select @tsql='select kdsj '+@tsql
+' from (select xsmd,kdsj,storemoney from lala) a '
+' pivot(max(storemoney) for xsmd in('+stuff(@tsql,1,1,'')+')) p '
exec(@tsql) /*
kdsj 5号停机坪 店名1 店名2 万达
------------ ----------- ----------- ----------- -----------
2013-06-26 2522 101 201 4047
2013-06-27 2595 102 202 1994(2 row(s) affected)
*/
你这里的nsert into lala
select '5号停机坪',231,'2013-06-26',2522 union all
select '5号停机坪',231,'2013-06-27',2595 union all
select '万达',220,'2013-06-26',4047 union all
select '万达',220,'2013-06-27',1994 union all
的店名是手动添加上去的,能不能是表里有的店名都自动显示上去。
你这里的nsert into lala
select '5号停机坪',231,'2013-06-26',2522 union all
select '5号停机坪',231,'2013-06-27',2595 union all
select '万达',220,'2013-06-26',4047 union all
select '万达',220,'2013-06-27',1994 union all
的店名是手动添加上去的,能不能是表里有的店名都自动显示上去。
是动态加,不是手动加的。
你这里的nsert into lala
select '5号停机坪',231,'2013-06-26',2522 union all
select '5号停机坪',231,'2013-06-27',2595 union all
select '万达',220,'2013-06-26',4047 union all
select '万达',220,'2013-06-27',1994 union all
的店名是手动添加上去的,能不能是表里有的店名都自动显示上去。
是动态加,不是手动加的。在吗?你这是存储过程的诶,我想要的不是存储过程。我要sql语句就可以了。