Pivot Help - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Pivot Help

Author

Topic

brendanb
Starting Member

7 Posts

Posted - 2013-08-13 : 03:05:50

Hi,

I Just need some help with a simple pivot table

I have the following data First Part is the Source

Item Group Price
-------+-----+-------------+
0000001 102 164.900000
0000001 103 164.900000
0000001 106 161.800000
0000003 100 164.900000
0000003 102 164.900000
0000003 103 164.900000
0000003 106 161.800000
0000007 100 164.900000
0000007 102 164.900000
0000007 103 164.900000
0000007 106 161.800000
0000008 100 164.900000
0000008 102 164.900000
0000008 103 164.900000
0000008 106 161.800000
-------+-----+-------------+

Which I would like to Pivot like the following

Item Group100 Group102 Group103 Group106
-------+-------------+-------------+-----------------+--------------+
0000001 64.900000 164.900000 164.900000 161.800000
0000003 164.900000 164.900000 161.800000 161.800000
0000007 164.900000 161.800000 164.900000 164.900000
0000008 161.800000 161.800000 161.800000 161.800000
-------+-------------+-------------+-----------------+--------------+

Any help would be appreciated

thanks
Brendan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-13 : 03:21:00

[code]DECLARE @Sample TABLE
(
Item CHAR(7) NOT NULL,
Grp TINYINT NOT NULL,
Price MONEY NOT NULL
);

INSERT @Sample
(
Item,
Grp,
Price
)
VALUES ('0000001', 102, 164.9000),
('0000001', 103, 164.9000),
('0000001', 106, 161.8000),
('0000003', 100, 164.9000),
('0000003', 102, 164.9000),
('0000003', 103, 164.9000),
('0000003', 106, 161.8000),
('0000007', 100, 164.9000),
('0000007', 102, 164.9000),
('0000007', 103, 164.9000),
('0000007', 106, 161.8000),
('0000008', 100, 164.9000),
('0000008', 102, 164.9000),
('0000008', 103, 164.9000),
('0000008', 106, 161.8000);

-- SwePeso
SELECT p.Item,
p.[100] AS Group100,
p.[102] AS Group102,
p.[103] AS Group103,
p.[106] AS Group106
FROM @Sample AS s
PIVOT (
MAX(s.Price)
FOR s.Grp IN ([100], [102], [103], [106])
) AS p;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources