ttable1
id typeId codeRmk codeValue
1 1 尺码 30码
2 1 尺码 31码
3 1 尺码 32码
4 1 尺码 33码
8 2 牌子 A
9 2 牌子 B
10 2 牌子 C
21 3 类别 毛1
22 3 类别 毛2
23 3 类别 毛3
24 3 类别 毛4
25 3 类别 毛5
26 3 类别 毛6
27 3 类别 毛7
显示成
尺码: 30码 32码 33码 0 0 0 0
牌子: A B C 0 0 0 0
类别: 毛1 毛2 毛3 毛4 毛5 毛6 毛7
以最长的为准。不够长补0
---测试数据---
if object_id('[ttable1]') is not null drop table [ttable1]
go
create table [ttable1]([id] int,[typeId] int,[codeRmk] varchar(4),[codeValue] varchar(4))
insert [ttable1]
select 1,1,'尺码','30码' union all
select 2,1,'尺码','31码' union all
select 3,1,'尺码','32码' union all
select 4,1,'尺码','33码' union all
select 8,2,'牌子','A' union all
select 9,2,'牌子','B' union all
select 10,2,'牌子','C' union all
select 21,3,'类别','毛1' union all
select 22,3,'类别','毛2' union all
select 23,3,'类别','毛3' union all
select 24,3,'类别','毛4' union all
select 25,3,'类别','毛5' union all
select 26,3,'类别','毛6' union all
select 27,3,'类别','毛7'
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')+'max(case when px='+px+' then codeValue else ''0'' end) as col'+px
from
(select distinct ltrim((select count(1)+1 from ttable1 where codeRmk=t.codeRmk and id<t.id))px from ttable1 t) tt
select
@sql='select codeRmk,'
+@sql
+' from (select *,ltrim((select count(1)+1 from ttable1 where codeRmk=t.codeRmk and id<t.id))px from ttable1 t) tt group by codeRmk'
exec (@sql)
---结果---
codeRmk col1 col2 col3 col4 col5 col6 col7
------- ---- ---- ---- ---- ---- ---- ----
尺码 30码 31码 32码 33码 0 0 0
类别 毛1 毛2 毛3 毛4 毛5 毛6 毛7
牌子 A B C 0 0 0 0
declare @sql varchar(8000)
set @sql = 'select codeRmk + '':''' + ' codeRmk '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then codeValue else ''0'' end) [codeValue' + cast(px as varchar) + ']'
from (select distinct px from (select codeRmk , codeValue , px = (select count(1) from tb where codeRmk = t.codeRmk and codeValue < t.codeValue) + 1 from tb t)m) as a
set @sql = @sql + ' from (select codeRmk , codeValue , px = (select count(1) from tb where codeRmk = t.codeRmk and codeValue < t.codeValue) + 1 from tb t)m group by codeRmk + '':'''
exec(@sql)
drop table tb
/*
codeRmk codeValue1 codeValue2 codeValue3 codeValue4 codeValue5 codeValue6 codeValue7
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
尺码: 30码 31码 32码 33码 0 0 0
类别: 毛1 毛2 毛3 毛4 毛5 毛6 毛7
牌子: A B C 0 0 0 0
*/