一个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)
*/

[解决办法]

引用:

Quote: 引用:

店名已经是动态的了,见以下测试.

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)
*/

你这里的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
的店名是手动添加上去的，能不能是表里有的店名都自动显示上去。

是动态加，不是手动加的。
[解决办法]
如果行转列时，字段个数不固定。SORRY，无法静态写一个SQL；只能动态，写成存储过程。