帮忙写一个 记录"有竖变横" 的 sql ?

[pdsnet]

现在表结构 :table1(id(自动增长),zsid,name)
1,A
1,B
1,C
1,D
1,E
1,F
想要的结果是 :
1,A,B,C,D,E,F 怎么实现?
把这些对应的 值 放在同一行上 怎么控制 ?

[游北亮]

http://topic.csdn.net/u/20080408/10/d9053bf3-82a6-4609-bc4f-1dce0f095e11.html

[游北亮]

http://topic.csdn.net/u/20080408/10/d9053bf3-82a6-4609-bc4f-1dce0f095e11.html

[daxiong105]

http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html

[zkcq2004]

你搜搜行列互换吧，应该有你想要的

[wzy_love_sly]

create table table1(zsid int,name varchar(50))
insert into table1 select 1 ,'a'
insert into table1 select 1 ,'b'
insert into table1 select 1 ,'c'
insert into table1 select 1 ,'d'
insert into table1 select 1 ,'e'
insert into table1 select 1 ,'f'

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when name='''+name+''' then name end) as ['+name+']'
from table1
exec('select zsid,'+@sql+' from table1 group by zsid')

zsid a b c d e f
1 a b c d e f

[叶子]

从你的需求上看就是行列转换。

[叶子]

5楼的正解!学习一下！

[live_7sky]

CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)
Go

--交叉表语句的实现：
--用于：交叉表的列数是确定的
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test
group by name

[lovehongyun]

5楼的就对.

网上搜行列转换.一大堆

[pdsnet]

谢谢你的解答.
还有一点问题
我的数据不值这么多 ..

insert into table1 select 1 ,'a'
insert into table1 select 1 ,'b'
insert into table1 select 1 ,'c'
insert into table1 select 1 ,'d'
insert into table1 select 1 ,'e'
insert into table1 select 1 ,'f'
这些也应该 动态的

[Adechen]

sql server2005中有个pivot函数
IVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式

例子
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

[yagebu1983]

关注+学习！！
帮你顶！！

[Coco孙]

5楼太牛了。。。。。。。。。。。。。。

[pdsnet]

8楼也很帅 ,都好厉害啊

[紫气东来_999]

搜索下 PIVOT

就OK了~~