sql server 2005表: test
字段:id int
product carchar(40)
数据
id product
1 A1
1 A2
1 A3
1 A4
1 A5
1 A6
1 A7
2 B1
2 B2
2 B3我想得到如下的结果
id product1 product2 product3 product4 product5
1 A1 A2 A3 A4 A5
2 B1 B2 B3就是把行的数据转化成列,每一个id最多得到5个product。
有没有简单的sql可以实现这个功能?
谢谢!
字段:id int
product carchar(40)
数据
id product
1 A1
1 A2
1 A3
1 A4
1 A5
1 A6
1 A7
2 B1
2 B2
2 B3我想得到如下的结果
id product1 product2 product3 product4 product5
1 A1 A2 A3 A4 A5
2 B1 B2 B3就是把行的数据转化成列,每一个id最多得到5个product。
有没有简单的sql可以实现这个功能?
谢谢!
insert @a select 1,'a1' union all
select 1,'a2' union all
select 1,'a3' union all
select 1,'a4' union all
select 1,'a5' union all
select 1,'a6' union all
select 1,'a7' union all
select 2,'a1' union all
select 2,'a2' union all
select 2,'a3'
select id,(select product where product like '_1')as product1,(select product where product like '_2')as product2,(select product where product like '_3')as product3,(select product where product like '_4')as product4,(select product where product like '_5')as product5 from @a
,(case when right(product,1)='2' then product) product2
,(case when right(product,1)='3' then product) product3
,(case when right(product,1)='4' then product) product4
,(case when right(product,1)='5' then product) product5
from test
max(product2) as product2,
max(product3) as product3,
max(product4) as product4,
max(product5) as product5
from (
select id,(case when (select count(*) from test where id=a.id and product<=a.product)=1 then product) product1
,(case when (select count(*) from test where id=a.id and product<=a.product)=2 then product end) product2
,(case when (select count(*) from test where id=a.id and product<=a.product)=3 then product end) product3
,(case when (select count(*) from test where id=a.id and product<=a.product)=4 then product end) product4
,(case when (select count(*) from test where id=a.id and product<=a.product)=5 then product end) product5
from test a
) as t
group by id
--不要认为写对了很容易
max(product2) as product2,
max(product3) as product3,
max(product4) as product4,
max(product5) as product5
from (
select id,(case when (select count(*) from test where id=a.id and product<=a.product)=1 then product end) product1
,(case when (select count(*) from test where id=a.id and product<=a.product)=2 then product end) product2
,(case when (select count(*) from test where id=a.id and product<=a.product)=3 then product end) product3
,(case when (select count(*) from test where id=a.id and product<=a.product)=4 then product end) product4
,(case when (select count(*) from test where id=a.id and product<=a.product)=5 then product end) product5
from test a
) as t
group by id
谢谢,
接分
declare @a table(id int,product varchar(100))
insert @a select 1,'a1' union all
select 1,'b2' union all
select 1,'c3' union all
select 1,'d4' union all
select 2,'e5' union all
select 2,'f6' union all
select 2,'g7' union all
select 3,'hw' union all
select 3,'ic' union all
select 3,'jf'
select id,
(select product from @a b where b.id=a.id and (select count(*) from @a
where product<=b.product and id=a.id group by id)=1) product1,
(select product from @a b where b.id=a.id and (select count(*) from @a
where product<=b.product and id=a.id group by id)=2) product2,
(select product from @a b where b.id=a.id and (select count(*) from @a
where product<=b.product and id=a.id group by id)=3) product3,
(select product from @a b where b.id=a.id and (select count(*) from @a
where product<=b.product and id=a.id group by id)=4) product4,
(select product from @a b where b.id=a.id and (select count(*) from @a
where product<=b.product and id=a.id group by id)=5) product5
from @a a group by id