有一表Test,表中有TurnNo,Qty两个字段,现在我要将行转换为列,即是列不为固定的,该Sql 怎样写?
假如有如下数据:
TurnNo,Qty
T1 100
T2 125
T3 168
转换为
T1,T2,T3
100 125 168假如有如下数据:
TurnNo,Qty
T1 100
T2 125
T3 168
T5 188
T6 10
转换为
T1,T2,T3,T5,T6
100 125 168 188 10
假如有如下数据:
TurnNo,Qty
T1 100
T2 125
T3 168
转换为
T1,T2,T3
100 125 168假如有如下数据:
TurnNo,Qty
T1 100
T2 125
T3 168
T5 188
T6 10
转换为
T1,T2,T3,T5,T6
100 125 168 188 10
解决方案 »
- 查询空格后的 字母
- 请进,求解一条SQL
- 求助:为什么这个存储过程无法返回字符串
- 在用MS SQL Server还原数据时出错。高手请进
- 数据库用户删除不了
- 创建表时提示maximum row size (16029) exceeds the maximum number of bytes per row (8060)!
- 两表查询问题,请高手进来看看
- 同一台服务器上两个数据库之间的其中2个表数据同步???
- 修改字段类型为“标识”,并设置该字段为主键的SQL语句怎么写??
- 怎么把图片(bmp或jpg)ACCESS数据库(access97)!!!急急急!!!!!!!!!!!
- dayofyear
- 如何使用sql语句插入主键列数据
set @sql=''
select @sql=@sql+',max(case TurnNo when '''+TurnNo+''' then qty else null end) as ['+TurnNo+']'
from test
group by TurnNoset @sql=stuff(@sql,1,1,'')exec('select '+@sql+' from test')
insert test select 'T1', 100
union all select 'T2', 125
union all select 'T3', 168
union all select 'T5', 188
union all select 'T6', 10declare @a varchar(1000)
select @a=isnull(@a+',','')+' sum(case when turnno='''+turnno+''' then qty end) ['+turnno+']' from [test] order by turnno
exec('select '+@a+' from test')
set @sql='select '
select @sql=@sql+'sum(case when TurnNo ='''+TurnNo+''' then Qty else 0 end) ''TurnNo'','
from t
set @sql=left(@sql,len(@sql)-1)
exec(@sql +'from t')
set @sql = 'select,'
select @sql = @sql + 'sum(case TurnNo when '''+TurnNo +'''
then Qty else 0 end) as '''+TurnNo+''','
from (select distinct TurnNo from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test'
exec(@sql)
go
create table t(TurnNo varchar(10),Qty int)
insert into t values('T1',100)
insert into t values('T2',125)
insert into t values('T3',168)insert into t values('T5',188)
insert into t values('T6',10)
declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+'sum(case when TurnNo ='''+TurnNo+''' then Qty else 0 end) '+TurnNo+','
from t
set @sql=left(@sql,len(@sql)-1)
print @sql
exec(@sql +' from t')
set @sql=''
select @sql=@sql+',sum(case TurnNo when '''+TurnNo+''' then qty else 0 end) as ['+TurnNo+']'
from test
group by TurnNo
set @sql=stuff(@sql,1,1,'')
set @sql='select '+@sql+' from test'
exec (@sql)结果:
T1 T2 T3 T5 T6
----------- ----------- ----------- ----------- -----------
100 125 168 188 10
EXEC sp_TransDisplayColorSizeQty 'vwSDorderCSBDDtl', 'sColorName', 'iQty',
'E79DD28A-410E-43B7-8153-7C42D5E9A803',
'WHERE sProductNo = ''''DCH060008'''' '
*/
ALTER PROCEDURE dbo.sp_TransDisplayColorSizeQty
(
@sTableName sysname, --表名
@sFixNameField varchar(1000), --颜色等字段
@sValueNameField varchar(1000), --值
@SizeGroupGUID uniqueidentifier, --尺码组GUID
@sWhere varchar(1000) --条件
)
AS
DECLARE @sExeSQL varchar(8000)
SELECT @sExeSQL = '
DECLARE @sSQL varchar(8000)
SET @sSQL = ''SELECT '+CASE WHEN @sFixNameField <> '' THEN @sFixNameField+',' ELSE '' END+'''
SELECT @sSQL = @sSQL + ''SUM(CASE CAST(SizeGUID AS VARCHAR(38))
WHEN ''''''+CAST(a.SizeGUID AS VARCHAR(38))+'''''' THEN '+@sValueNameField+' ELSE 0 END) AS ''''''+a.sSizeName+'''''',''
FROM (
SELECT DISTINCT TOP 100 PERCENT iID, GUID AS SizeGUID, sName AS sSizeName
FROM vwSize a
WHERE SizeGroupGUID = '''+CAST(@SizeGroupGUID AS VARCHAR(36))+'''
ORDER BY iID
) a
SELECT @sSQL = LEFT (@sSQL, len(@sSQL) - 1) + '' --, SUM('+@sValueNameField+') AS ['+@sValueNameField+']
FROM '+@sTableName+'
'+@sWhere+''
+ CASE WHEN @sFixNameField <> '' THEN ' GROUP BY '+@sFixNameField ELSE '' END
+ ''''
+' EXEC(''SELECT * FROM (''+@sSQL+'') a '')'--增加Select * from () a是为了让Delphi种的字段可以编辑
--SELECT @sSQL AS sSQL
EXEC(@sExeSQL)
--SELECT @sExeSQL AS sExeSQL--我转尺码的通用过程,改下尺码的表名就可以用了。