SQL查询

Question

结构
 姓名    学号   兴趣    
 张三     1     足球
 张三     1     篮球
 张三     1     游泳
 李四     2     羽毛球希望得到结果如下
姓名  学号  兴趣一  兴趣二  兴趣三 （这里的“兴趣X”表头个数 已兴趣最多的人为主 张三有3个兴趣）
张三   1    足球     篮球    游泳
李四   2    羽毛球 
这个SQL怎么写～

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-14 16:15:46
-- Verstion:
--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 
-- May 26 2009 14:24:20 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([姓名] varchar(4),[学号] int,[兴趣] varchar(6))
insert [tb]
select '张三',1,'足球' union all
select '张三',1,'篮球' union all
select '张三',1,'游泳' union all
select '李四',2,'羽毛球'
--------------开始查询--------------------------
select
  姓名,学号,
  max(case id when 1 then 兴趣 else '' end) as '兴趣1',
  max(case id when 2 then 兴趣 else '' end) as '兴趣2',
  max(case id when 3 then 兴趣 else '' end) as '兴趣3'
from
  (select id=row_number()over(partition by 姓名 order by getdate()),* from tb)t
group by
  姓名,学号
----------------结果----------------------------
/* 姓名   学号          兴趣1    兴趣2    兴趣3
---- ----------- ------ ------ ------
张三   1           足球     篮球     游泳
李四   2           羽毛球           (2 行受影响)*/

Answer 2

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-14 16:15:04
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
Jul  9 2008 14:43:34 
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([姓名] NVARCHAR(10),[学号] INT,[兴趣] NVARCHAR(10))
INSERT [tb]
SELECT N'张三',1,N'足球' UNION ALL
SELECT N'张三',1,N'篮球' UNION ALL
SELECT N'张三',1,N'游泳' UNION ALL
SELECT N'李四',2,N'羽毛球'
GO
--SELECT * FROM [tb]-->SQL查询如下:SELECT * 
FROM (
SELECT RN='兴趣'+LTRIM(ROW_NUMBER()OVER(PARTITION BY [姓名] ORDER BY [学号])),* 
FROM TB
) A
PIVOT(MAX(兴趣) FOR RN IN(兴趣1,兴趣2,兴趣3)) B
ORDER BY 学号
/*
姓名         学号          兴趣1        兴趣2        兴趣3
---------- ----------- ---------- ---------- ----------
张三         1           足球         篮球         游泳
李四         2           羽毛球        NULL       NULL(2 行受影响)
*/

Answer 3

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-14 16:15:04
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
Jul  9 2008 14:43:34 
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([姓名] NVARCHAR(10),[学号] INT,[兴趣] NVARCHAR(10))
INSERT [tb]
SELECT N'张三',1,N'足球' UNION ALL
SELECT N'张三',1,N'篮球' UNION ALL
SELECT N'张三',1,N'游泳' UNION ALL
SELECT N'李四',2,N'羽毛球'
GO
--SELECT * FROM [tb]-->SQL2005动态查询如下:
DECLARE @S VARCHAR(8000)
SELECT @S=ISNULL(@S+',兴趣','兴趣')+RN 
FROM (SELECT DISTINCT LTRIM(ROW_NUMBER()OVER(PARTITION BY [姓名] ORDER BY [学号])) RN FROM TB) T
EXEC('
SELECT * 
FROM (
SELECT RN=''兴趣''+LTRIM(ROW_NUMBER()OVER(PARTITION BY [姓名] ORDER BY [学号])),* 
FROM TB
) A
PIVOT(MAX(兴趣) FOR RN IN('+@s+')) B
ORDER BY 学号
')
/*
姓名         学号          兴趣1        兴趣2        兴趣3
---------- ----------- ---------- ---------- ----------
张三         1           足球         篮球         游泳
李四         2           羽毛球        NULL       NULL(2 行受影响)
*/