日期:2014-05-17 浏览次数:20489 次
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-22 15:21:03
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([项目] varchar(4),[产品] varchar(1),[价格] int)
insert [tb]
select '广东','a',1 union all
select '广东','b',3 union all
select '广东','c',10 union all
select '广西','a',5 union ALL
select '中国','f',9 union all
select '中国','g',8
--------------开始查询--------------------------
--declare @s nvarchar(4000)
--set @s=''
--Select @s=@s+',''产品''=max(case when [px]='+quotename([px],'''')+' then [产品] else '''' end),''价格''=max(case when [px]='+quotename([px],'''')+' then [价格] else '''' end)'
--from (SELECT px=ROW_NUMBER()OVER(PARTITION BY 项目 ORDER BY GETDATE()),产品,价格 FROM tb )AS a group BY a.px,产品,价格
--exec('select [项目]'+@s+' from (SELECT px=ROW_NUMBER()OVER(PARTITION BY 项目 ORDER BY GETDATE()),* FROM tb )AS a group by [项目]')
declare @sql varchar(8000)
set @sql = 'select 项目 '