pid type jiage1 jiage2 jiage3
1 a 200 300 400
1 b 100 500 400
2 b 100 500 400
2 c 300 500 400
将上面的数据转化为下面的格式,
--------------------------------------pid type jiage1 jiage2 jiage3 type1 jiage11 jiage21 jiage31
1 a 200 300 400 b 100 500 400
2 b 100 500 400 c 300 500 400
pid每个数字表示一个用户,type(类型) jiage1(价格) jiage2(价格) jiage3 (价格) 即每个用户一条记录,列名要求变 如:type1,jiage11 ,jiage21 ,jiage31
多谢
1 a 200 300 400
1 b 100 500 400
2 b 100 500 400
2 c 300 500 400
将上面的数据转化为下面的格式,
--------------------------------------pid type jiage1 jiage2 jiage3 type1 jiage11 jiage21 jiage31
1 a 200 300 400 b 100 500 400
2 b 100 500 400 c 300 500 400
pid每个数字表示一个用户,type(类型) jiage1(价格) jiage2(价格) jiage3 (价格) 即每个用户一条记录,列名要求变 如:type1,jiage11 ,jiage21 ,jiage31
多谢
-- Author: liangCK 小梁
-- Date : 2008-11-15 16:25:36
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (pid INT,type VARCHAR(1),jiage1 INT,jiage2 INT,jiage3 INT)
INSERT INTO @T
SELECT 1,'a',200,300,400 UNION ALL
SELECT 1,'b',100,500,400 UNION ALL
SELECT 2,'b',100,500,400 UNION ALL
SELECT 2,'c',300,500,400--SQL查询如下:;WITH Liang
AS
(
SELECT
*,
pix=ROW_NUMBER()
OVER(PARTITION BY pid
ORDER BY type)
FROM
@T
)
SELECT
pid,
MAX(CASE pix WHEN 1 THEN type ELSE '' END) AS type,
MAX(CASE pix WHEN 1 THEN jiage1 ELSE 0 END) AS jiage1,
MAX(CASE pix WHEN 1 THEN jiage2 ELSE 0 END) AS jiage2,
MAX(CASE pix WHEN 1 THEN jiage3 ELSE 0 END) AS jiage3 ,
MAX(CASE pix WHEN 2 THEN type ELSE '' END) AS type1,
MAX(CASE pix WHEN 2 THEN jiage1 ELSE 0 END) AS jiage11,
MAX(CASE pix WHEN 2 THEN jiage2 ELSE 0 END) AS jiage12,
MAX(CASE pix WHEN 2 THEN jiage3 ELSE 0 END) AS jiage13
FROM
Liang
GROUP BY
pid/*
pid type jiage1 jiage2 jiage3 type1 jiage11 jiage12 jiage13
----------- ---- ----------- ----------- ----------- ----- ----------- ----------- -----------
1 a 200 300 400 b 100 500 400
2 b 100 500 400 c 300 500 400(2 行受影响)*/
-- Author: liangCK 小梁
-- Date : 2008-11-15 16:25:36
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (pid INT,type VARCHAR(1),jiage1 INT,jiage2 INT,jiage3 INT)
INSERT INTO @T
SELECT 1,'a',200,300,400 UNION ALL
SELECT 1,'b',100,500,400 UNION ALL
SELECT 2,'b',100,500,400 UNION ALL
SELECT 2,'c',300,500,400--SQL查询如下:;WITH Liang
AS
(
SELECT
*,
pix=ROW_NUMBER()
OVER(PARTITION BY pid
ORDER BY type)
FROM
@T
)
SELECT
pid,
MAX(CASE pix WHEN 1 THEN type ELSE '' END) AS type,
MAX(CASE pix WHEN 1 THEN jiage1 ELSE 0 END) AS jiage1,
MAX(CASE pix WHEN 1 THEN jiage2 ELSE 0 END) AS jiage2,
MAX(CASE pix WHEN 1 THEN jiage3 ELSE 0 END) AS jiage3 ,
MAX(CASE pix WHEN 2 THEN type ELSE '' END) AS type1,
MAX(CASE pix WHEN 2 THEN jiage1 ELSE 0 END) AS jiage11,
MAX(CASE pix WHEN 2 THEN jiage2 ELSE 0 END) AS jiage12,
MAX(CASE pix WHEN 2 THEN jiage3 ELSE 0 END) AS jiage13
FROM
Liang
GROUP BY
pid/*
pid type jiage1 jiage2 jiage3 type1 jiage11 jiage12 jiage13
----------- ---- ----------- ----------- ----------- ----- ----------- ----------- -----------
1 a 200 300 400 b 100 500 400
2 b 100 500 400 c 300 500 400(2 行受影响)*/
declare @a table (pid int,type varchar(2),jiage1 int,jiage2 int,jiage3 int)
insert into @a select 1,'a',200,300,400
union all select 1,'b',100,500,400
union all select 2,'b',100,500,400
union all select 2,'c',300,500,400
select * ,identity(int,1,1) as 序号 into #a from @a
--select * from #a
select a.pid,a.type,a.jiage1,a.jiage2,a.jiage3,b.type,b.jiage1,b.jiage2,b.jiage3 from
(select * from #a a where not exists (select 1 from #a where a.pid=pid and a.序号>序号)) a
join
(select * from #a a where not exists (select 1 from #a where a.pid=pid and a.序号<序号)) b
on a.pid=b.pid1 a 200 300 400 b 100 500 400
2 b 100 500 400 c 300 500 400