行列转换，数据构造，有提供原始生成数据，你写select 就好。

[nandou1989]

CREATE TABLE [dbo].[CTB](
	Id int,
	CustomerId int,
	ConfigurationId int,
	Value varchar(200)
)
GO
INSERT CTB(Id, CustomerId, ConfigurationId, Value) VALUES
  (1, 1, 100, 'aaa')
, (2, 1, 110, 'true')
, (3, 1, 120, 'false')
, (4, 2, 100, 'bbb')
, (5, 3, 100, 'ccc')
, (6, 4, 100, 'ddd')
GO
/*
这是我想要生成的数据形式
--------------------------------------------------------------------------
	ConfigurationId\CustomerId		1		2		3		4
		100							aaa		bbb		ccc		ddd
		110							true	NULL	NULL	NULL
		120							false	NULL	NULL	NULL
*/	

[siegebaoniu]

[Quote=引用 6 楼 baiying15 的回复:]

select ConfigurationId,
max(case CustomerId when 1 then Value else 0 end)as 1
max(case CustomerId when 2 then Value else 0 end)as 2
max(case CustomerId when 3 then Value else 0……
[/Quote]
在将 varchar 值 'aaa' 转换成数据类型 int 时失败。
是不是还要convert一下？

[siegebaoniu]

奇怪啊，我用动态的时候并没有报错...

[fengyun142415]

select ConfigurationId as [ConfigurationId\CustomerId],
max(case CustomerId when 1 then value end) as [1],
max(case CustomerId when 2 then value end) as [2],
max(case CustomerId when 3 then value end) as [3],
max(case CustomerId when 4 then value end) as [4]
from CTB
group by ConfigurationId

[「已注销」]

[Quote=引用 14 楼 siegebaoniu 的回复:]

奇怪啊，我用动态的时候并没有报错...
[/Quote]
我告诉你玩什么你没报错，因为你自己根本没run，
只要你run过这句sql，不可能不报错，除非你表结构不一样。

[「已注销」]

[Quote=引用 13 楼 siegebaoniu 的回复:]

[Quote=引用 6 楼 baiying15 的回复:]

select ConfigurationId,
max(case CustomerId when 1 then Value else 0 end)as 1
max(case CustomerId when 2 then Value else 0 end)as 2
max(case Cus……
[/Quote]
“else 0”的写法全是错的。

[sinpoal]

http://blog.csdn.net/sinpoal/archive/2009/11/09/4785441.aspx

[nandou1989]

都乱掉了，再发一下试试。

CREATE TABLE [dbo].[CTB](
	Id int,
	CustomerId int,
	ConfigurationId int,
	Value varchar(200)
)
GO
INSERT CTB(Id, CustomerId, ConfigurationId, Value) VALUES
  (1, 1, 100, 'aaa')
, (2, 1, 110, 'true')
, (3, 1, 120, 'false')
, (4, 2, 100, 'bbb')
, (5, 3, 100, 'ccc')
, (6, 4, 100, 'ddd')
GO
/*
这是我想要生成的数据形式
--------------------------------------------------------------------------
ConfigurationId\CustomerId	1	2	3	4
	100			aaa	bbb	ccc	ddd
	110			true	NULL	NULL	NULL
	120			false	NULL	NULL	NULL
*/	

[bancxc]

select ConfigurationId,max(case  CustomerId when 1 then value else 0 end) [1]
 max(case  CustomerId when 2 then value else 0 end) [2]
 max(case  CustomerId when 2 then value else 0 end) [3]
 max(case  CustomerId when 2 then value else 0 end) [4]
group by ConfigurationId

[bancxc]

select ConfigurationId,max(case  CustomerId when 1 then value else 0 end) [1],
 max(case  CustomerId when 2 then value else 0 end) [2],
 max(case  CustomerId when 3 then value else 0 end) [3],
 max(case  CustomerId when 4 then value else 0 end) [4]
from [CTB]
group by ConfigurationId

[guguda2008]

IF OBJECT_ID('CTB') IS NOT NULL DROP TABLE CTB
GO
CREATE TABLE [dbo].[CTB](
    Id int,
    CustomerId int,
    ConfigurationId int,
    Value varchar(200)
)
GO
INSERT CTB(Id, CustomerId, ConfigurationId, Value) VALUES
  (1, 1, 100, 'aaa')
, (2, 1, 110, 'true')
, (3, 1, 120, 'false')
, (4, 2, 100, 'bbb')
, (5, 3, 100, 'ccc')
, (6, 4, 100, 'ddd')
GO


DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=ISNULL(@SQL+',','SELECT ConfigurationId,')
+'
MAX(CASE CustomerId WHEN '+LTRIM(CustomerId)+' THEN VALUE ELSE NULL END) '
FROM CTB
GROUP BY CustomerId
EXEC (@SQL+'
FROM CTB
GROUP BY ConfigurationId')
/*
100	aaa	bbb	ccc	ddd
110	true	NULL	NULL	NULL
120	false	NULL	NULL	NULL
*/

[「已注销」]

select ConfigurationId,max(case  CustomerId when 1 then value else NULL end) [1],
 max(case  CustomerId when 2 then value ELSE NULL end) [2],
 max(case  CustomerId when 3 then value else NULL end) [3],
 max(case  CustomerId WHEN 4 then value else NULL end) [4]
from [CTB]
group by ConfigurationId

[baiying15]

select ConfigurationId,
max(case CustomerId when 1 then Value else 0 end)as 1
max(case CustomerId when 2 then Value else 0 end)as 2
max(case CustomerId when 3 then Value else 0 end)as 3
max(case CustomerId when 4 then Value else 0 end)as 4
from CTB
group by ConfigurationId

[baiying15]

我晕；你们动作都那么快。。。我看到帖子的时候还没人回；这回上去都已经纳闷多了。。汗~~~

[baiying15]

select ConfigurationId,
max(case CustomerId when 1 then Value else 0 end)as 1
max(case CustomerId when 2 then Value else 0 end)as 2
max(case CustomerId when 3 then Value else 0 end)as 3
max(case CustomerId when 4 then Value else 0 end)as 4
from CTB
group by ConfigurationId

[nandou1989]

[Quote=引用 8 楼 baiying15 的回复:]

SQL code

select ConfigurationId,
max(case CustomerId when 1 then Value else 0 end)as 1
max(case CustomerId when 2 then Value else 0 end)as 2
max(case CustomerId when 3 then Value else 0 end)as 3
ma……
[/Quote]

我只是写出了四个CustomerID，其实表里有　四十个以上的客户。
所以，相比还是动态语句好一点，因为可能会有新客户，或者删除某个客户。

[guguda2008]

[Quote=引用 9 楼 nandou1989 的回复:]

引用 8 楼 baiying15 的回复:

SQL code

select ConfigurationId,
max(case CustomerId when 1 then Value else 0 end)as 1
max(case CustomerId when 2 then Value else 0 end)as 2
max(case CustomerId when 3 ……
[/Quote]
参见4L

[baiying15]

declare @sql varchar(8000)
set @sql = 'select ConfigurationId '
select @sql = @sql + ' , max(case CustomerId when ''' + CustomerId + ''' then Value else 0 end) [' + CustomerId + ']'
from (select distinct CustomerId from tb) as a
set @sql = @sql + ' from tb group by ConfigurationId'
exec(@sql) 

[baiying15]

动态的。。你试哈；没有测试。