Click here to Skip to main content
14,301,033 members
Rate this:
Please Sign up or sign in to vote.
SNO  PRODNAME    ENERGY PROTEIN  CALCIUM
1      ABC        156    134      195
2      DEF        123    345      345
3      ERT        134    345      456

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?
Posted
Updated 8-Dec-13 20:27pm
v2
Comments
Ganesh Raja 9-Dec-13 2:36am
   
Can you explain the Question clearly..?
Rate this:
Please Sign up or sign in to vote.

Solution 1

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


For further information, please see: Using PIVOT and UNPIVOT[^]
   
v2
Comments
Amir Mahfoozi 9-Dec-13 2:39am
   
+5 !
Maciej Los 9-Dec-13 2:41am
   
Thank you ;)
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
   
Comments
Maciej Los 9-Dec-13 2:42am
   
+5
Amir Mahfoozi 9-Dec-13 2:46am
   
Thank you :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100