--创建表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 备注 流水号 网点号
max(case when FIELENAME='SerialNo' then FIELEVALUE end)SerialNo ,
max(case when FIELENAME='PointCode' then FIELEVALUE end)PointCode
from ProductInitialDataTables group by productEnName
productEnName,
max(case FIELENAME when 'Memory' then FIELEVALUE end)Memory,
max(case FIELENAME when 'SerialNo' then FIELEVALUE end)SerialNo ,
max(case FIELENAME when 'PointCode' then FIELEVALUE end)PointCode
from
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 行)
*/--sql 2005
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 行)
*/
我是要将这个SQL语句变成存储过程的,Memory,SerialNo,SerialNo 这三个值是动态的传过来的,@aa='Memory,SerialNo,SerialNo'.
动态传过来的不止这三个,可能还会是@aa='sdf,wer,wer,wer,rt,dgh,ert,dfgh,rty,erth,......'
里面具体有多少也是不固定的。数据中的值是查出来的,你是看不到数据的,如:“备注,流水号,网点号......”你是看不到得,不能直接的用这些值的。。要加的条件
条件ProductEnName='ADMS20' and TableName='AssessmentMemory'
里面的值也是动态的高手帮助???????
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