一行数据以多行展示的sql语句
如我有以下数据:
Year Family P1 P2
2013 Material 654 478
如何使用sql将其变成如下形式展现：
Year PIndex Family Volume
2013 1 Material 654
2013 2 Material 478
请高手帮助！多谢。
[解决办法]
select year, 1 Pindex,family ,P1 from table_name
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
[解决办法]
MSSQL2005及以上版本：
CREATE TABLE t1
(
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
-----------------------
YearPIndexFamilyVolume
20131Material654
20132Material478
20133Material239
20134Material345
20135Material187
