急急，向各位请教一下下，sqlserver怎么实现行转列呢，谢谢！

[xuqunying0545]

求一sql语句 行转列COL1 COL2 COL3 COL4
----------------------------
表里面得内容为：
COL1 COL2 COL3 COL4
----------------------------
A B 1 125
A B 2 250
A B 3 330
A B 4 440
A B 5 570
........

现在要显示为
COL1 COL2 COL3 COL4 COL5 COL6 COL7 .....
------------------------------------------------------------
A B 1 125 250 330 440 ....

总之是COL1和COL2 相等的 都显示在一行上面 怎么写这样的sql语句？

[li123mao]

if not object_id('Class') is null
drop table Class
Go
Create table Class([COL1] nvarchar(2),[COL2] nvarchar(2),[COL3] int,[COL4] int)
Insert Class
select N'A',N'B',1,125 union all
select N'A',N'B',2,250 union all
select N'A',N'B',3,330 union all
select N'A',N'B',4,440 union all
select N'A',N'B',5,570
Go

declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([COL3])+'=max(case when [COL3]='+quotename([COL3],'''')+' then [COL4] else 0 end)'
from Class group by [COL1],[COL2],[COL3]
exec('select [COL1],[COL2]'+@s+' from Class group by [COL1],[COL2]')

--------------------------
结果
A B 125 250 330 440 570

[feixianxxx]

if not object_id('Class') is null 
    drop table Class 
Go 
Create table Class([COL1] nvarchar(2),[COL2] nvarchar(2),[COL3] int,[COL4] int) 
Insert Class 
select N'A',N'B',1,125 union all 
select N'A',N'B',2,250 union all 
select N'A',N'B',3,330 union all 
select N'A',N'B',4,440 union all 
select N'A',N'B',5,570 
Go 

declare @s nvarchar(4000) 
set @s='' 
Select @s=@s+',[col'+rtrim([COL3]+2)+']=max(case when [COL3]='+quotename([COL3],'''')+' then [COL4] else 0 end)' 
from Class group by [COL1],[COL2],[COL3] 

exec('select [COL1],[COL2]'+@s+' from Class group by [COL1],[COL2]') 
/*
COL1 COL2 col3        col4        col5        col6        col7
---- ---- ----------- ----------- ----------- ----------- -----------
A    B    125         250         330         440         570

*/

[xuqunying0545]

ALTER procedure test

@L_START_TIME VARCHAR(20),--查询开始时间
@L_END_TIME VARCHAR(20) --查询结束时间
AS

BEGIN
declare @str1 nvarchar(4000)
declare @str2 nvarchar(4000)
declare @str3 nvarchar(4000)
declare @str4 nvarchar(4000)
declare @flag varchar(1)
declare @adv_error INT --保存运行时的ERROR
declare @start_time varchar(20)
declare @end_time varchar(20)
set @start_time=''''+@L_START_TIME+''''
set @end_time=''''+@L_END_TIME+''''
set @str1=''
set @str2=''
set @flag='1'
set @str4='(SELECT [AREA_NAME],[ERROR_TYPE1],SUM(ITEM_COUNT) KINDERRORQTY FROM V_R_ERRORKIND_QUERY WHERE ERROR_FLAG='+''+@flag+''+'and IN_LINE_TIME>=convert(datetime,'+@start_time+') and IN_LINE_TIME<=convert(datetime,'+@end_time+')'+' GROUP BY AREA_NAME,ERROR_TYPE1)'
set @str3='(SELECT A1.AREA_NAME,A1.TOTALQTY,A1.ERRORQTY,A2.ERROR_TYPE1,A2.KINDERRORQTY
FROM (SELECT [AREA_NAME],SUM(ITEM_COUNT) TOTALQTY,(SELECT SUM(ITEM_COUNT)
FROM V_R_ERRORKIND_QUERY WHERE ERROR_FLAG='+''+@flag+''+'and IN_LINE_TIME>=convert(datetime,'+@start_time+') and IN_LINE_TIME<=convert(datetime,'+@end_time+')'+' GROUP BY AREA_NAME) ERRORQTY FROM V_R_ERRORKIND_QUERY where IN_LINE_TIME>=convert(datetime,'+@start_time+') and IN_LINE_TIME<=convert(datetime,'+@end_time+')'+' GROUP BY AREA_NAME) A1
LEFT JOIN
(SELECT [AREA_NAME],[ERROR_TYPE1],SUM(ITEM_COUNT) KINDERRORQTY FROM V_R_ERRORKIND_QUERY WHERE ERROR_FLAG='+''+@flag+''+'and IN_LINE_TIME>=convert(datetime,'+@start_time+') and IN_LINE_TIME<=convert(datetime,'+@end_time+')'+' GROUP BY AREA_NAME,ERROR_TYPE1)A2
ON A1.AREA_NAME=A2.AREA_NAME)A3 group by AREA_NAME'
--Select @str1=@str1+','+quotename('ERROR_TYPE1')+'=max(case when ERROR_TYPE1='+quotename('[ERROR_TYPE1]','''')+' then [ErrorTypeQty] else 0 end)
--from'+@str4+' group by[ERROR_TYPE1]'

--Select @str1=@str1+','+quotename([ERROR_TYPE1])+'=max(case when [ERROR_TYPE1]='+quotename([ERROR_TYPE1],'''')+' then [ErrorTypeQty] else 0 end)'
--from (SELECT [AREA_NAME],[ERROR_TYPE1],SUM(ITEM_COUNT) KINDERRORQTY FROM V_R_ERRORKIND_QUERY WHERE ERROR_FLAG=1 GROUP BY AREA_NAME,ERROR_TYPE1) a5 group by[ERROR_TYPE1]
Select @str1=@str1+','+quotename([ERROR_TYPE1])+'=max(case when [ERROR_TYPE1]='+quotename([ERROR_TYPE1],'''')+' then [ErrorTypeQty] else 0 end)' from (SELECT [AREA_NAME],[ERROR_TYPE1],SUM(ITEM_COUNT) KINDERRORQTY FROM V_R_ERRORKIND_QUERY WHERE ERROR_FLAG=1 and IN_LINE_TIME<=convert(datetime,@end_time) GROUP BY AREA_NAME,ERROR_TYPE1) a5 group by[ERROR_TYPE1]


SELECT @adv_error =@@ERROR

select @str2='select AREA_NAME,TOTALQTY,ERRORQTY'+@str1+' from '+@str3
SELECT @adv_error =@@ERROR
END

[xuqunying0545]

Select @str1=@str1+','+quotename([ERROR_TYPE1])+'=max(case when [ERROR_TYPE1]='+quotename([ERROR_TYPE1],'''')+' then [ErrorTypeQty] else 0 end)' from (SELECT [AREA_NAME],[ERROR_TYPE1],SUM(ITEM_COUNT) KINDERRORQTY FROM V_R_ERRORKIND_QUERY WHERE ERROR_FLAG=1 and IN_LINE_TIME<=convert(datetime,@end_time) GROUP BY AREA_NAME,ERROR_TYPE1) a5 group by[ERROR_TYPE1]

为什么加个　and IN_LINE_TIME<=convert(datetime,@end_time)会执行到这一步直接跳出去，否则则正常执行。

[xuqunying0545]

谢谢大家，问题解决了

[JonasFeng]

精华贴里很多的。

[xuqunying0545]

还有一个问题，我怎么把动态执行的结果集绑定给datagridview呢

[xuqunying0545]

用exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')
这种方法，列又不固定，我怎么处理结果集呢，怎么绑定数据源呢

[feixianxxx]

--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
	drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select 	@s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')


生成静态:

select 
	[Student],
	[数学]=max(case when [Course]='数学' then [Score] else 0 end),
	[物理]=max(case when [Course]='物理' then [Score] else 0 end),
	[英语]=max(case when [Course]='英语' then [Score] else 0 end),
	[语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
	Class 
group by [Student]

GO
动态:

declare @s nvarchar(4000)
Select 	@s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

生成静态:
select * 
from 
	Class 
pivot 
	(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78

(2 行受影响)
*/

------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)

--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select 	@s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))

生成动态:

select 
	[Student],
	[数学]=max(case when [Course]='数学' then [Score] else 0 end),
	[物理]=max(case when [Course]='物理' then [Score] else 0 end),
	[英语]=max(case when [Course]='英语' then [Score] else 0 end),
	[语文]=max(case when [Course]='语文' then [Score] else 0 end),
	[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from 
	Class 
group by [Student]

go

--2005方法:

动态:

declare @s nvarchar(4000)
Select 	@s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')

生成静态:

select 
	[Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
	(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
	(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 

生成格式:

/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337

(2 行受影响)
*/

go

--2、列转行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
	drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go

--2000:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]

go
--2005:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

go
select 
	Student,[Course],[Score] 
from 
	Class 
unpivot 
	([Score] for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78

(8 行受影响)
*/

[华夏小卒]

-- 收藏

quotename(Name)  --这个什么意思？

我查了一下帮助，下面的实例，这个函数有什么作用，还是有点不明白，哪位指点一下
SELECT QUOTENAME('abc[]def')
下面是结果集：
[abc[]]def]

注意，字符串"abc[]def"中的右括号有两个，用于表示转义符。

[sharon8259]

学习先！

[xuqunying0545]

说明一下是不固定列的，谢谢