在SQL SERVER 2008中无人值守的表 [英] UNPIVOTING A TABLE IN SQL SERVER 2008

问题描述

SNO  PRODNAME    ENERGY PROTEIN  CALCIUM
1      ABC        156    134      195
2      DEF        123    345      345
3      ERT        134    345      456

i具有上述表格结构。我必须 unpivot 它以便我得到以下输出像这样

i have the above table structure . i have to unpivot it so that i should get the following output LIKE THIS

SNO    PRODNAME   NUTRITENT    VALUE
1       ABC        ENERGY       156
1       ABC        PROTEIN      134
1       ABC        CALCIUM       195

我该怎么做？

how can i do this?

推荐答案

测试它：

Test it:

DECLARE @tmp TABLE (SNO INT IDENTITY(1,1), PRODNAME VARCHAR(30), ENERGY INT, PROTEIN INT, CALCIUM INT)

INSERT INTO @tmp (PRODNAME, ENERGY, PROTEIN, CALCIUM)
VALUES('ABC', 156, 134, 195),
    ('DEF', 123, 345, 345),
    ('ERT', 134, 345, 456)

SELECT *
FROM  @tmp


SELECT SNO, PRODNAME, NUTRITENT, [VALUE]
FROM (
    SELECT SNO, PRODNAME, ENERGY, PROTEIN, CALCIUM
    FROM @tmp
    ) AS Pvt
UNPIVOT ([VALUE] FOR NUTRITENT IN ([ENERGY],[PROTEIN],[CALCIUM])) AS UnPvt

结果：

Results:

1	ABC	ENERGY	156
1	ABC	PROTEIN	134
1	ABC	CALCIUM	195
2	DEF	ENERGY	123
2	DEF	PROTEIN	345
2	DEF	CALCIUM	345
3	ERT	ENERGY	134
3	ERT	PROTEIN	345
3	ERT	CALCIUM	456

如需了解更多信息，请参阅： Usin g PIVOT和UNPIVOT [ ^ ]

这是：

Here it is :

declare @table1  table 
(
id int,
prodname nvarchar(10),
energy int,
protein int,
calcium int
)

insert into @table1 values (1,'ABC', 156, 134, 195)
insert into @table1 values (2,'DEF', 123, 345, 345)
insert into @table1 values (3,'ERT', 134, 345, 456)

select * from 
(select id,  prodname, energy, protein, calcium from @table1) p
unpivot
(prodcount for prdd in([energy],[protein],[calcium])  ) up

好​​运

Good Luck

这篇关于在SQL SERVER 2008中无人值守的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！