22,300
社区成员




select * from sb where erbi
DECLARE @tableHTML NVARCHAR(max)=''
create table #t(IndexID INT,Otype NVARCHAR(100),colSpan INT,rowSpan INT)
INSERT INTO #T(IndexID,Otype,colSpan,rowSpan)
SELECT 0,N'文本',4,1 union all
SELECT 1,N'图片',1,1 union all
SELECT 2,N'文本',1,1 union all
SELECT 3,N'标签',2,1 union all
SELECT 4,N'图片',4,1 union all
SELECT 5,N'文本',4,1 union all
SELECT 6,N'图片',4,1 union all
SELECT 7,N'单选',1,1 union all
SELECT 8,N'单选',1,1 union all
SELECT 9,N'单选',1,1 union all
SELECT 10,N'单选',1,1 union all
SELECT 11,N'文本',2,1 union all
SELECT 12,N'文本',2,1 union all
SELECT 13,N'文本',4,1
;WITH AA AS (
select *, case when isnull(p.cols,0)%4=0 then 1 else 0 end ChangeRow
,case when min(IndexID)over()=IndexID then 1 else 0 end firstrow
,case when max(IndexID)over()=IndexID then 1 else 0 end lastrow
from #T as t1
outer apply(select sum(colspan) from #t as tt where tt.IndexID<t1.IndexID) p(cols)
)
--select * from AA
SELECT @tableHTML =@tableHTML+'<table>'+CONVERT(NVARCHAR(max),(
SELECT case when firstrow=1 then '<tr>' else '' end ,
[td/@colspan]=colspan,td=case when /*非表单*/Otype=N'标签' then 'aaaa' when Otype=N'图片' then '<img src="aaa.jpg" />' else
'<input type="'+CASE A.Otype WHEN N'文本' THEN 'text' WHEN N'单选' THEN 'radio' END +'" name="input'+ltrim(IndexID)+'"/>' end,''
,case when ChangeRow=1 then '</tr>' else '' end+case when ChangeRow=1 and lastrow=0 then '<tr>' else '' end
FROM AA as A ORDER BY A.IndexID FOR XML PATH(''),TYPE
))+N'</table>'
SET @tableHTML=REPLACE(REPLACE(@tableHTML,'<','<'),'>','>')
select @tableHTML
SELECT @tableHTML =@tableHTML+'<table>'+CONVERT(NVARCHAR(max),(
SELECT case when firstrow=1 then '<tr>' else '' end ,
[td/@colspan]=colspan,td=case when /*非表单*/Otype=N'标签' then 'aaaa' when Otype=N'图片' then '<img src="aaa.jpg" />' else
'<input type="'+CASE A.Otype WHEN N'文本' THEN 'text' WHEN N'单选' THEN 'radio' END +'" name="input'+ltrim(IndexID)+'"/>' end,''
,case when ChangeRow=1 then '</tr>' else '' end+case when ChangeRow=1 and lastrow=0 then '<tr>' else '' end
FROM AA as A ORDER BY A.IndexID FOR XML PATH(''),TYPE
))+N'</table>'
DECLARE @tableHTML NVARCHAR(max)=''
create table #t(IndexID INT,Otype NVARCHAR(100),colSpan INT,rowSpan INT)
INSERT INTO #T(IndexID,Otype,colSpan,rowSpan)
SELECT 0,N'文本',4,1 union all
SELECT 1,N'图片',1,1 union all
SELECT 2,N'文本',1,1 union all
SELECT 3,N'标签',2,1 union all
SELECT 4,N'图片',4,1 union all
SELECT 5,N'文本',4,1 union all
SELECT 6,N'图片',4,1 union all
SELECT 7,N'单选',1,1 union all
SELECT 8,N'单选',1,1 union all
SELECT 9,N'单选',1,1 union all
SELECT 10,N'单选',1,1 union all
SELECT 11,N'文本',2,1 union all
SELECT 12,N'文本',2,1 union all
SELECT 13,N'文本',4,1
;WITH AA AS (
select *, case when sum(colspan)over(order by IndexID)%4=0 then 1 else 0 end ChangeRow
,case when min(IndexID)over()=IndexID then 1 else 0 end firstrow
,case when max(IndexID)over()=IndexID then 1 else 0 end lastrow
from #T
)
--select * from AA
SELECT @tableHTML =@tableHTML+'<table>'+CONVERT(NVARCHAR(max),(
SELECT case when firstrow=1 then '<tr>' else '' end ,
[td/@colspan]=colspan,td=case when /*非表单*/Otype=N'标签' then 'aaaa' when Otype=N'图片' then '<img src="aaa.jpg" />' else
'<input type="'+CASE A.Otype WHEN N'文本' THEN 'text' WHEN N'单选' THEN 'radio' END +'" name="input'+ltrim(IndexID)+'"/>' end,''
,case when ChangeRow=1 then '</tr>' else '' end+case when ChangeRow=1 and lastrow=0 then '</tr>' else '' end
FROM AA as A ORDER BY A.IndexID FOR XML PATH(''),TYPE
))+N'</table>'
SET @tableHTML=REPLACE(REPLACE(@tableHTML,'<','<'),'>','>')
select @tableHTML
<table>
<tr>
<td colspan="4">
<input type="text" name="input0" />
</td>
</tr>
</tr>
<td colspan="1">
<img src="aaa.jpg" />
</td>
<td colspan="1">
<input type="text" name="input2" />
</td>
<td colspan="2">aaaa</td>
</tr>
</tr>
<td colspan="4">
<img src="aaa.jpg" />
</td>
</tr>
</tr>
<td colspan="4">
<input type="text" name="input5" />
</td>
</tr>
</tr>
<td colspan="4">
<img src="aaa.jpg" />
</td>
</tr>
</tr>
<td colspan="1">
<input type="radio" name="input7" />
</td>
<td colspan="1">
<input type="radio" name="input8" />
</td>
<td colspan="1">
<input type="radio" name="input9" />
</td>
<td colspan="1">
<input type="radio" name="input10" />
</td>
</tr>
</tr>
<td colspan="2">
<input type="text" name="input11" />
</td>
<td colspan="2">
<input type="text" name="input12" />
</td>
</tr>
</tr>
<td colspan="4">
<input type="text" name="input13" />
</td>
</tr>
</table>
DECLARE @tableHTML NVARCHAR(max)=''
create table #t(IndexID INT,Otype NVARCHAR(100),colSpan INT,rowSpan INT)
INSERT INTO #T(IndexID,Otype,colSpan,rowSpan)
SELECT 0,N'文本',4,1 union all
SELECT 1,N'图片',1,1 union all
SELECT 2,N'文本',1,1 union all
SELECT 3,N'标签',2,1 union all
SELECT 4,N'图片',4,1 union all
SELECT 5,N'文本',4,1 union all
SELECT 6,N'图片',4,1 union all
SELECT 7,N'单选',1,1 union all
SELECT 8,N'单选',1,1 union all
SELECT 9,N'单选',1,1 union all
SELECT 10,N'单选',1,1 union all
SELECT 11,N'文本',2,1 union all
SELECT 12,N'文本',2,1 union all
SELECT 13,N'文本',4,1
;WITH AA AS (
select *, case when sum(colspan)over(order by IndexID)%4=0 then 1 else 0 end ChangeRow,case when min(IndexID)over()=IndexID then 1 else 0 end firstrow from #T
)
--select * from AA
SELECT @tableHTML =@tableHTML+'<table>'+CONVERT(NVARCHAR(max),(
SELECT case when firstrow=1 then '<tr>' else '' end ,
[td/@colspan]=colspan,td=case when /*非表单*/Otype=N'标签' then 'aaaa' when Otype=N'图片' then '<img src="aaa.jpg"' else
'<input type="'+CASE A.Otype WHEN N'文本' THEN 'text' WHEN N'单选' THEN 'radio' END +'" name="input'+ltrim(IndexID)+'">' end,''
,case when ChangeRow=1 then '</tr><tr>' else '' end
FROM AA as A ORDER BY A.IndexID FOR XML PATH(''),TYPE
))+N'</tr></table>'
SET @tableHTML=REPLACE(REPLACE(@tableHTML,'<','<'),'>','>')
select @tableHTML
<table><tr><td colspan="4"><input type="text" name="input0"></td></tr><tr><td colspan="1"><img src="aaa.jpg"</td><td colspan="1"><input type="text" name="input2"></td><td colspan="2">aaaa</td></tr><tr><td colspan="4"><img src="aaa.jpg"</td></tr><tr><td colspan="4"><input type="text" name="input5"></td></tr><tr><td colspan="4"><img src="aaa.jpg"</td></tr><tr><td colspan="1"><input type="radio" name="input7"></td><td colspan="1"><input type="radio" name="input8"></td><td colspan="1"><input type="radio" name="input9"></td><td colspan="1"><input type="radio" name="input10"></td></tr><tr><td colspan="2"><input type="text" name="input11"></td><td colspan="2"><input type="text" name="input12"></td></tr><tr><td colspan="4"><input type="text" name="input13"></td></tr><tr></tr></table>
DECLARE @tableHTML NVARCHAR(max)=''
;WITH A(IndexID,Otype,colSpan,rowSpan)AS (
SELECT 0,N'文本',4,2
)
SELECT @tableHTML =@tableHTML+'<table>'+CONVERT(NVARCHAR(max),(
SELECT
[td/@colspan]=colspan,td='<input type="'+CASE A.Otype WHEN N'文本' THEN 'text' WHEN N'图片' THEN 'img' END +'" name="text1">'
FROM A ORDER BY A.IndexID FOR XML PATH('tr'),TYPE
))+N'</table>'
SET @tableHTML=REPLACE(REPLACE(@tableHTML,'<','<'),'>','>')
PRINT @tableHTML
结果
<table><tr><td colspan="4"><input type="text" name="text1"></td></tr></table>