如UserName Regions
张1 延边
张1 遵义
张1 延吉
张1 滕州
转成UserName 无列名 无列名 无列名 无列名 操作
张1 延边 遵义 延吉 滕州 添加要求动态SQL语句,谢谢了
张1 延边
张1 遵义
张1 延吉
张1 滕州
转成UserName 无列名 无列名 无列名 无列名 操作
张1 延边 遵义 延吉 滕州 添加要求动态SQL语句,谢谢了
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-09 14:35:09
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([UserName] varchar(3),[Regions] varchar(4))
insert [tb]
select '张1','延边' union all
select '张1','遵义' union all
select '张1','延吉' union all
select '张1','滕州'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select UserName '
select @sql = @sql + ' , max(case px when ''' + ltrim(px) + ''' then Regions else '''' end)'
from (select distinct px from (select px=row_number()over(partition by username order by getdate()),* from tb)t) as a
set @sql = @sql + ' from (select px=row_number()over(partition by username order by getdate()),* from tb)t group by UserName'
exec(@sql) ----------------结果----------------------------
/* UserName
-------- ---- ---- ---- ----
张1 延边 遵义 延吉 滕州(1 行受影响)
*/
fredrickhu求解答,马上给分
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-09 14:35:09
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([UserName] varchar(3),[Regions] varchar(4))
insert [tb]
select '张1','延边' union all
select '张1','遵义' union all
select '张1','延吉' union all
select '张1','滕州'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select UserName '
select @sql = @sql + ' , max(case px when ''' + ltrim(px) + ''' then Regions else '''' end)'
from (select distinct px from (select px=row_number()over(partition by username order by getdate()),'''添加''' AS 操作,* from tb)t) as a
set @sql = @sql + ', ''添加'' as 操作 from (select px=row_number()over(partition by username order by getdate()),* from tb)t group by UserName'
exec(@sql) ----------------结果----------------------------
/*UserName 操作
-------- ---- ---- ---- ---- ----
张1 延边 遵义 延吉 滕州 添加(1 行受影响)*/