queary table 1 to look like table 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

queary table 1 to look like table 2

Author

Topic

Pavlo01
Starting Member

6 Posts

Posted - 2013-05-30 : 18:08:01

Can someone help me please!
I was asked to query table 1 to look like table 2 using SQL

Table 1
Cat# CatDesc Year Period_01 Period_02 Period_03 Period_04
03 Standard IC 2013 7890 4450 1010 2430
04 Engnring IC 2013 280 74321 8362 222403

Table 2
Cat# CatDesc Year Period Amount
03 Standard IC 2013 01 7890
03 Standard IC 2013 02 4450
03 Standard IC 2013 03 1010
03 Standard IC 2013 04 2430
04 Engnring IC 2013 01 280
04 Engnring IC 2013 02 74321
04 Engnring IC 2013 03 8362
04 Engnring IC 2013 04 222403

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-30 : 18:16:12

You can use the UNPIVOT operator like shown below

SELECT  *
FROM    ( SELECT    [Cat#] ,
                    CatDesc ,
                    Year ,
                    Period_01 AS [01] ,
                    Period_02 AS [02] ,
                    Period_03 AS [03] ,
                    Period_04 AS [04]
          FROM      Table1
        ) s UNPIVOT
( Amount FOR Period IN ( [01], [02], [03] ) ) U

Pavlo01
Starting Member

6 Posts

Posted - 2013-05-30 : 19:13:35

Thank You So Much You've Been A Great Help

Last question,
what would the query be if I wanted to do the reverse,
from table 2 to table 1.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-30 : 20:05:16

Use PIVOT Operation:
[CODE]
SELECT [Cat#],
CatDesc,
Year,
[01] as Period_01,
[02] as Period_02,
[03] as Period_03,
[04] as Period_04
FROM (SELECT [Cat#],
CatDesc,
Year,
Amount,
Period FROM @TEMP2) AS D
PIVOT(SUM(Amount) FOR Period IN([01], [02], [03], [04])) AS P;

[/CODE]

Pavlo01
Starting Member

6 Posts

Posted - 2013-05-31 : 03:43:05

Can you explain to me how hte pivot and unpivot functions work.

Why is there a sum() in Pivot and not in Unpivot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 03:50:21

Pivot is a aggregate operation as you're effectively trying to convert a row value to a column. Hence you need to use an aggregate function like SUM,MAX,AVG etc

Unpivot does the opposite. It converts column values from row onto multiple row values based on the column heading

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources