--> 生成测试数据表: [student] IF OBJECT_ID('[student]') IS NOT NULL DROP TABLE [student] GO CREATE TABLE [student] ([Name] [nvarchar](10),[Sex] [int]) INSERT INTO [student] SELECT 'a','1' UNION ALL SELECT 'b','0' UNION ALL SELECT 'c','1' UNION ALL SELECT 'd','0' UNION ALL SELECT 'e','1' -->SQL查询如下: SELECT * FROM [student] PIVOT(MIN(sex) FOR name IN (a, b, c, d, e)) b /* a b c d e ----------- ----------- ----------- ----------- ----------- 1 0 1 0 1(1 行受影响) */
-->动态SQL查询如下: DECLARE @s VARCHAR(MAX) SELECT @s=ISNULL(@s+',','')+QUOTENAME(Name) FROM student GROUP BY Name EXEC(' SELECT * FROM [student] PIVOT(MIN(sex) FOR name IN ('+@s+')) b ') /* a b c d e ----------- ----------- ----------- ----------- ----------- 1 0 1 0 1(1 行受影响) */
create table student(name varchar(10),sex varchar(1)) insert into student select 'a','1' union all select 'b','0' union all select 'c','1' union all select 'd','0' union all select 'e','1'select * from studentdeclare @sql varchar(1000) set @sql='' select @sql=@sql+',['+max(name)+']=sum(case name when '''+max(name)+''' then sex else 0 end)' from student group by name set @sql=substring(@sql,2,len(@sql)-1) print @sql exec('select '+@sql+' from student -- group by name')--结果 a b c d e --------------------- 1 0 1 0 1
--> 生成测试数据表: [student]
IF OBJECT_ID('[student]') IS NOT NULL
DROP TABLE [student]
GO
CREATE TABLE [student] ([Name] [nvarchar](10),[Sex] [int])
INSERT INTO [student]
SELECT 'a','1' UNION ALL
SELECT 'b','0' UNION ALL
SELECT 'c','1' UNION ALL
SELECT 'd','0' UNION ALL
SELECT 'e','1'
-->SQL查询如下:
SELECT *
FROM [student]
PIVOT(MIN(sex) FOR name IN (a, b, c, d, e)) b
/*
a b c d e
----------- ----------- ----------- ----------- -----------
1 0 1 0 1(1 行受影响)
*/
DECLARE @s VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(Name) FROM student GROUP BY Name
EXEC('
SELECT *
FROM [student]
PIVOT(MIN(sex) FOR name IN ('+@s+')) b
')
/*
a b c d e
----------- ----------- ----------- ----------- -----------
1 0 1 0 1(1 行受影响)
*/
insert into student
select 'a','1'
union all select 'b','0'
union all select 'c','1'
union all select 'd','0'
union all select 'e','1'select * from studentdeclare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(name)+']=sum(case name when '''+max(name)+''' then sex else 0 end)'
from student group by name set @sql=substring(@sql,2,len(@sql)-1)
print @sql
exec('select '+@sql+' from student -- group by name')--结果
a b c d e
---------------------
1 0 1 0 1