一起数据以多行展示的sql语句
日期：2014-05-17 浏览次数：20514 次
一行数据以多行展示的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及以上版本：
如我有以下数据:
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
-----------------------
Year PIndex Family Volume
2013 1 Material 654
2013 2 Material 478
2013 3 Material 239
2013 4 Material 345
2013 5 Material 187
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
