一个sql行转列的问题
怎么把他转成行，如下面的图
，storemoney是每天详细收入。下图最左边那排序号是日期。 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) 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号停机坪 万达
------------ ----------- -----------
2013-06-26 2522 4047
2013-06-27 2595 1994
(2 row(s) affected)
*/
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 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)
*/
