如我有以下数据:
Year Family P1 P2
2013 Material 654 478
如何使用sql将其变成如下形式展现:Year PIndex Family Volume
2013 1 Material 654
2013 2 Material 478
请高手帮助!多谢。
Year Family P1 P2
2013 Material 654 478
如何使用sql将其变成如下形式展现:Year PIndex Family Volume
2013 1 Material 654
2013 2 Material 478
请高手帮助!多谢。
union all
select year, 2 Pindex,family ,P2 from table_name
Year Family P1 P2
2013 Material 654 478*/declare @tb table([Year] int,Family varchar(10),p1 int,p2 int)
insert into @tb values(2013,'Material',654,478)
select * from @tb
select t2.Year,t2.Family,t2.p3 from @tb
unpivot (
p3 for p4 in([p1],[p2])
) as t2
<tr><td>年度</td><td>家庭</td><td>P1</td></tr>
<tr><td>年度</td><td>家庭</td><td>P2</td></tr>
next
P有很多,p1~p20,那不是要写20个union啊。
这是不是sqlserver?没见过。
P有很多,p1~p20,那不是要写20个union啊。
可以参考2楼的写法
(
nian INT,
family VARCHAR(20),
p1 INT,
p2 INT,
p3 INT,
p4 INT,
p5 INT
)
INSERT INTO t1
SELECT 2013,'Material',654,478,239,345,187
SELECT * FROM t1;WITH aaa AS
(
SELECT nian,family,Volume FROM t1
UNPIVOT (Volume FOR p0 IN (p1,p2,p3,p4,p5) ) AS p
)
SELECT nian AS [Year],ROW_NUMBER() OVER(ORDER BY GETDATE()) AS PIndex,Family,Volume FROM aaa -----------------------
Year PIndex Family Volume
2013 1 Material 654
2013 2 Material 478
2013 3 Material 239
2013 4 Material 345
2013 5 Material 187
select
t2.Year,replace(t2.PIndex,'p',''),t2.Family,t2.Volume
from offtake
unpivot (
volume for PIndex in([p1],[p2],[p3],[p4],[p5],[p6],
[p7],[p8],[p9],[p10],[p11],[p12],[p13])
) as t2
sql2005还有这个功能,为什么我才知道,孤陋寡闻了啊!!谢谢各位热心人士。