行列转换问题!请教高人??--创建表T_ProductInitialDataTablesCREATE TABLE T_ProductInitialDataTables
( [SerialNo] int NOT NULL IDENTITY (1, 1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo]))insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');--现在查询的结果
select * from T_ProductInitialDataTables
SerialNo productEnName TableName FIELENAME FIELEVALUE41 ADMS20 AssessmentMemory PointCode 网点号
42 ADMS20 AssessmentMemory SerialNo 流水号
43 ADMS20 AssessmentMemory Memory 备注
44 ADMS20 AssessmentMemory PointCode 网点号
45 ADMS20 AssessmentMemory SerialNo 流水号
46 ADMS20 AssessmentMemory Memory 备注
--如何得到这样的结果????
productEnName Memory SerialNo PointCodeADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号
( [SerialNo] int NOT NULL IDENTITY (1, 1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo]))insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');--现在查询的结果
select * from T_ProductInitialDataTables
SerialNo productEnName TableName FIELENAME FIELEVALUE41 ADMS20 AssessmentMemory PointCode 网点号
42 ADMS20 AssessmentMemory SerialNo 流水号
43 ADMS20 AssessmentMemory Memory 备注
44 ADMS20 AssessmentMemory PointCode 网点号
45 ADMS20 AssessmentMemory SerialNo 流水号
46 ADMS20 AssessmentMemory Memory 备注
--如何得到这样的结果????
productEnName Memory SerialNo PointCodeADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号
declare @T_ProductInitialDataTables table (SerialNo int,productEnName varchar(6),TableName varchar(16),FIELENAME varchar(9),FIELEVALUE varchar(6))
insert into @T_ProductInitialDataTables
select 41,'ADMS20','AssessmentMemory','PointCode','网点号' union all
select 42,'ADMS20','AssessmentMemory','SerialNo','流水号' union all
select 43,'ADMS20','AssessmentMemory','Memory','备注' union all
select 44,'ADMS20','AssessmentMemory','PointCode','网点号' union all
select 45,'ADMS20','AssessmentMemory','SerialNo','流水号' union all
select 46,'ADMS20','AssessmentMemory','Memory','备注'select productEnName,
max(case FIELEVALUE when '网点号' then '网点号' else '' end) as PointCode,
max(case FIELEVALUE when '流水号' then '流水号' else '' end) as SerialNo,
max(case FIELEVALUE when '备注' then '备注' else '' end) as Memory
from @T_ProductInitialDataTables
group by productEnName
union allselect productEnName,
max(case FIELEVALUE when '网点号' then '网点号' else '' end) as PointCode,
max(case FIELEVALUE when '流水号' then '流水号' else '' end) as SerialNo,
max(case FIELEVALUE when '备注' then '备注' else '' end) as Memory
from @T_ProductInitialDataTables
group by productEnName
/*
productEnName PointCode SerialNo Memory
------------- --------- -------- ------
ADMS20 网点号 流水号 备注
ADMS20 网点号 流水号 备注
*/
select productEnName,
max(case FIELEVALUE when '网点号' then '网点号' else '' end) as PointCode,
max(case FIELEVALUE when '流水号' then '流水号' else '' end) as SerialNo,
max(case FIELEVALUE when '备注' then '备注' else '' end) as Memory
from T_ProductInitialDataTables group by productEnName
union all
select productEnName,
max(case FIELEVALUE when '网点号' then '网点号' else '' end) as PointCode,
max(case FIELEVALUE when '流水号' then '流水号' else '' end) as SerialNo,
max(case FIELEVALUE when '备注' then '备注' else '' end) as Memory
from T_ProductInitialDataTables group by productEnName
select productEnName ,
max(case fieldname when 'Memory' then fielevalue else '' end) Memory,
max(case fieldname when 'SerialNo' then fielevalue else '' end) SerialNo,
max(case fieldname when 'PointCode' then fielevalue else '' end) PointCode
from T_ProductInitialDataTables
group by (serialno - 1)/3 , productEnName/*
productEnName Memory SerialNo PointCode
-------------------- ---------- ---------- ----------
ADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号(所影响的行数为 2 行)
*/--这个保险些.
select productEnName ,
max(case fieldname when 'Memory' then fielevalue else '' end) Memory,
max(case fieldname when 'SerialNo' then fielevalue else '' end) SerialNo,
max(case fieldname when 'PointCode' then fielevalue else '' end) PointCode
from (select t.* , px = (select count(1) from T_ProductInitialDataTables where productEnName = t.productEnName and SerialNo < t.SerialNo) + 1 from T_ProductInitialDataTables t) m
group by (px - 1)/3 , productEnName
/*
productEnName Memory SerialNo PointCode
-------------------- ---------- ---------- ----------
ADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号(所影响的行数为 2 行)
*/
select productEnName ,
max(case fieldname when 'Memory' then fielevalue else '' end) Memory,
max(case fieldname when 'SerialNo' then fielevalue else '' end) SerialNo,
max(case fieldname when 'PointCode' then fielevalue else '' end) PointCode
from T_ProductInitialDataTables
group by (serialno - 1)/3 , productEnName/*
productEnName Memory SerialNo PointCode
-------------------- ---------- ---------- ----------
ADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号(所影响的行数为 2 行)
*/--这个保险些.
select productEnName ,
max(case fieldname when 'Memory' then fielevalue else '' end) Memory,
max(case fieldname when 'SerialNo' then fielevalue else '' end) SerialNo,
max(case fieldname when 'PointCode' then fielevalue else '' end) PointCode
from (select t.* , px = row_number() over(partition by productEnName order by serialno) from T_ProductInitialDataTables t) m
group by (px - 1)/3 , productEnName
/*
productEnName Memory SerialNo PointCode
-------------------- ---------- ---------- ----------
ADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号(所影响的行数为 2 行)
*/
--创建表T_ProductInitialDataTablesCREATE TABLE T_ProductInitialDataTables
( [SerialNo] int NOT NULL IDENTITY (1, 1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo]))insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');
GOdeclare @sql varchar(8000)
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then ''' + FIELEVALUE + ''' else '''' end)[' + FieldName + ']'
from (select distinct FieldName,FIELEVALUE from T_ProductInitialDataTables)t
select @sql = @sql + ' from T_ProductInitialDataTables group by productEnName'
exec(@sql + ' union all ' + @sql)drop table T_ProductInitialDataTables
/*productEnName Memory PointCode SerialNo
-------------------------------------------------------------------------------------------------------------------------------- ------ --------- --------
ADMS20 备注 网点号 流水号
ADMS20 备注 网点号 流水号(2 行受影响)
productEnName ,
max(case fieldname when 'Memory' then fielevalue else '' end) Memory,
max(case fieldname when 'SerialNo' then fielevalue else '' end) SerialNo,
max(case fieldname when 'PointCode' then fielevalue else '' end) PointCode
from
(select * , px = (select count(1) from T_ProductInitialDataTables where productEnName = t.productEnName and SerialNo < t.SerialNo) + 1 from T_ProductInitialDataTables t)t
group by
(px - 1)/3 , productEnName
--创建表T_ProductInitialDataTablesCREATE TABLE T_ProductInitialDataTables
( [SerialNo] int NOT NULL IDENTITY (1, 1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo]))insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');
GOdeclare @sql varchar(8000)
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then ''' + FIELEVALUE + ''' else '''' end)[' + FieldName + ']'
from (select distinct FieldName,FIELEVALUE from T_ProductInitialDataTables)t
select @sql = @sql + ' from T_ProductInitialDataTables group by productEnName,(SerialNo - 1)/3'
exec(@sql)drop table T_ProductInitialDataTables/*productEnName Memory PointCode SerialNo
-------------------------------------------------------------------------------------------------------------------------------- ------ --------- --------
ADMS20 备注 网点号 流水号
ADMS20 备注 网点号 流水号(2 行受影响)
--动态改善的这样子--创建表T_ProductInitialDataTablesCREATE TABLE T_ProductInitialDataTables
( [SerialNo] int NOT NULL IDENTITY (1, 1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo]))insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');
GOdeclare @sql varchar(8000)
declare @cnt int
select @cnt = count(distinct FieldName) from T_ProductInitialDataTables group by productEnName,TableName
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then ''' + FIELEVALUE + ''' else '''' end)[' + FieldName + ']'
from (select distinct FieldName,FIELEVALUE from T_ProductInitialDataTables)t
select @sql = @sql + ' from T_ProductInitialDataTables group by productEnName,(SerialNo - 1)/' + ltrim(@cnt)
exec(@sql)drop table T_ProductInitialDataTables/*productEnName Memory PointCode SerialNo
-------------------------------------------------------------------------------------------------------------------------------- ------ --------- --------
ADMS20 备注 网点号 流水号
ADMS20 备注 网点号 流水号
ADMS20 备注 网点号 流水号(3 行受影响)
use tempdb;
/*
CREATE TABLE T_ProductInitialDataTables
(
[SerialNo] int NOT NULL IDENTITY (1,1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo])
);insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue])
values
('ADMS20','AssessmentMemory','PointCode','网点号');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue])
values
('ADMS20','AssessmentMemory','SerialNo','流水号');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue])
values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue])
values
('ADMS20','AssessmentMemory','PointCode','网点号');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue])
values
('ADMS20','AssessmentMemory','SerialNo','流水号');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue])
values
('ADMS20','AssessmentMemory','Memory','备注');
*/
select
t1. ProductEnName,
MAX(case when t1.FieleValue = '备注' then t1.FieleValue end) as [Memory],
MAX(case when t1.FieleValue = '流水号' then t1.FieleValue end) as [Memory],
MAX(case when t1.FieleValue = '网点号' then t1.FieleValue end) as [Memory]
from
(
select *,ROW_NUMBER() over(partition by t.FieleValue order by t.SerialNo) as [sortnum]
from T_ProductInitialDataTables as t
) as t1
group by t1.sortnum,t1. ProductEnName;
declare @sql varchar(8000)
set @sql='select productEnName '
select @sql = @sql + ', max(case FIELEVALUE when ''' + FIELEVALUE + ''' then FIELEVALUE else 0 end) [' + FIELEVALUE + ']'
from (select distinct FIELEVALUE from T_ProductInitialDataTables) as a
set @sql = @sql + ' from T_ProductInitialDataTables group by productEnName'
--select @sql
exec(@sql)
select
t1. ProductEnName,
MAX(case when t1.FieleValue = '备注' then t1.FieleValue end) as [Memory],
MAX(case when t1.FieleValue = '流水号' then t1.FieleValue end) as [Memory],
MAX(case when t1.FieleValue = '网点号' then t1.FieleValue end) as [Memory]
from
(
select *,ROW_NUMBER() over(partition by t.FieleValue order by t.SerialNo) as [sortnum]
from T_ProductInitialDataTables as t
) as t1
where t1.ProductEnName = 'ADMS20' and t1.TableName = 'AssessmentMemory'
group by t1.sortnum,t1. ProductEnName;
declare @sql varchar(8000)
declare @cnt int
select @cnt = count(distinct FieldName) from T_ProductInitialDataTables group by productEnName,TableName
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then ''' + FIELEVALUE + ''' else '''' end)[' + FieldName + ']'
from (select distinct FieldName,FIELEVALUE from T_ProductInitialDataTables)t
select @sql = @sql + ' from T_ProductInitialDataTables where ...... group by productEnName,(SerialNo - 1)/' + ltrim(@cnt)
exec(@sql)
我是要将这个SQL语句变成存储过程的,Memory,SerialNo,SerialNo 这三个值是动态的传过来的,@aa='Memory,SerialNo,SerialNo'.
动态传过来的不止这三个,可能还会是@aa='sdf,wer,wer,wer,rt,dgh,ert,dfgh,rty,erth,......'
里面具体有多少也是不固定的。数据中的值是查出来的,你是看不到数据的,如:“备注,流水号,网点号......”你是看不到得,不能直接的用这些值的。。高手帮助???????
里面的值也是动态的
create proc get_all(@field varchar(8000),@where varchar(8000))
as
begin
declare @sql nvarchar(4000)
declare @str nvarchar(4000)
declare @cnt int
set @str = N'select @cnt = count(distinct FieldName) from T_ProductInitialDataTables ' + @where + ' group by productEnName,TableName'
exec sp_executesql @str,N'@cnt int output',@cnt output
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then FIELEVALUE else '''' end)[' + FieldName + ']'
from (select substring(@field,number,charindex(',',@field + ',',number) - number) as FieldName
from master..spt_values
where [type] = 'p' and number between 1 and len(@field)
and substring(','+@field,number,1) = ','
)t
select @sql = @sql + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName,(SerialNo - 1)/' + ltrim(@cnt)
exec(@sql)
end
goexec dbo.get_all 'Memory,SerialNo',''drop proc get_all
as
begin
declare @sql nvarchar(4000)
declare @str nvarchar(4000)
declare @cnt int
set @str = N'select @cnt = count(distinct FieldName) from T_ProductInitialDataTables ' + @where + ' group by productEnName,TableName'
exec sp_executesql @str,N'@cnt int output',@cnt output
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then FIELEVALUE else '''' end)[' + FieldName + ']'
from (select substring(@field,number,charindex(',',@field + ',',number) - number) as FieldName
from master..spt_values
where [type] = 'p' and number between 1 and len(@field)
and substring(','+@field,number,1) = ','
)t
select @sql = @sql + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName,(SerialNo - 1)/' + ltrim(@cnt)
exec(@sql)
end
goexec dbo.get_all 'Memory,SerialNo',''drop proc get_all
请问,(SerialNo-1)是什么意思??这里搞不懂还是不能进行
这里必须要找到规律,这里的1也应该是动态的
ALTER PROC get_all(
@FieldName VARCHAR(MAX),
@where VARCHAR(MAX),
@productEnName VARCHAR(256),
@TableName VARCHAR(256)
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
DECLARE @record_SQL NVARCHAR(MAX)
DECLARE @str NVARCHAR(MAX)
DECLARE @str1 NVARCHAR(MAX)
DECLARE @cnt INT
DECLARE @Parametricvariable NVARCHAR(100)
DECLARE @Transition INT
DECLARE @cnt1 INT
DECLARE @SunMax INT
DECLARE @Contrast INT
DECLARE @Count INT
SET @Parametricvariable='1'
SET @Transition=1
SET @cnt=1
SET @Contrast=2
--通过条件 productEnName=@productEnName and TableName=@TableName得到要查的表中的SerialNo 相应的最小的值
SELECT @Contrast=min(SerialNo)
FROM T_ProductInitialDataTables
WHERE (productEnName=@productEnName AND TableName=@TableName)
--得到数据表中应有的列数
SET @str1 = N'SELECT @cnt1 = count(distinct FieldName) FROM T_ProductInitialDataTables '+
@where + ' GROUP BY productEnName,TableName'
--查询出数据表中(SerialNo-@Parametricvariable)/+ltrim(@cnt)的最小值放在变量@Count中
SET @record_SQL ='SELECT @Count=MIN((SerialNo-'+@Parametricvariable+')/'+ltrim(@cnt1)+')'
SELECT @record_SQL = @record_SQL
FROM (SELECT substring(@FieldName,number,charindex(',',@FieldName + ',',number) - number) AS FieldName
FROM master..spt_values WHERE [type] = 'p' AND number BETWEEN 1 AND len(@FieldName)
AND substring(','+@FieldName,number,1) = ',')t
SELECT @record_SQL = @record_SQL + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName'
EXEC sp_executesql @record_SQL,N'@Count int output',@Count OUTPUT
SET @str = N'select @cnt = count(distinct FieldName) from T_ProductInitialDataTables '+
@where + ' GROUP BY productEnName,TableName'
EXEC sp_executesql @str,N'@cnt int output',@cnt OUTPUT
SET @SQL = 'select productEnName,'+'(SerialNo-'+@Parametricvariable+')/'+ltrim(@cnt)+' AS ID '
SELECT @SQL = @SQL + ',max(case FieldName when ''' + FieldName + ''' then FIELEVALUE else '''' end)[' + FieldName + ']'
FROM (SELECT substring(@FieldName,number,charindex(',',@FieldName + ',',number) - number) AS FieldName FROM master..spt_values
WHERE [type] = 'p' AND number BETWEEN 1 AND len(@FieldName) AND substring(','+@FieldName,number,1) = ',')t
SELECT @SQL = @SQL + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName,(SerialNo-'+@Parametricvariable+')/' + ltrim(@cnt)
SET @Count=(@Contrast-@Parametricvariable)/@cnt
SET @Transition=@Contrast-@cnt*@Count
SET @Parametricvariable=CAST(@Transition AS VARCHAR)
PRINT @Parametricvariable
SET @str = N'select @cnt = count(distinct FieldName) from T_ProductInitialDataTables ' + @where + ' group by productEnName,TableName'
EXEC sp_executesql @str,N'@cnt int output',@cnt OUTPUT
SET @SQL = 'select productEnName,'+'(SerialNo-'+@Parametricvariable+')/'+ltrim(@cnt)+' AS ID '
SELECT @SQL = @SQL + ',max(case FieldName when ''' + FieldName + ''' then FIELEVALUE else '''' end)[' + FieldName + ']'
FROM (SELECT substring(@FieldName,number,charindex(',',@FieldName + ',',number) - number) AS FieldName
FROM master..spt_values
WHERE [type] = 'p' and number BETWEEN 1 AND len(@FieldName)
AND substring(','+@FieldName,number,1) = ',')t
SELECT @SQL = @SQL + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName,(SerialNo-'+@Parametricvariable+')/' + ltrim(@cnt)
EXEC sp_executesql @record_SQL,N'@Count int output',@Count OUTPUT
EXEC sp_executesql @SQL,N'@Count int output',@Count OUTPUT
END
GOexec dbo.get_all 'test1,test2,test3,test4,test5,test6,test7,test8,test9,test10','
WHERE TableName = ''Table10'' AND productEnName=''FINANCE10''','FINANCE10','Table10'
这才能得到,不管字段,表名,表的英文名称如何的变化,都能得到我想要的结果,在这里(SerialNo-1)中的1是变话得,这个1是随着表字段的多少来变化的,他应该是一个变量,这里有个公式,但是不好描述(SerialNo(相对的最小值)-1)/ltrim(@cnt)=查出结果后的相对最小值
这个公式不知道能否看懂?