Pivot Table - 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 2008 Forums

Transact-SQL (2008)

Pivot Table

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2014-10-22 : 22:42:52

Hi,

I had the following table result:

Modem_ID Date Time Sent_Total Received_Total
COM10 2014-10-22 1100 1 1
COM10 2014-10-22 1200 4 4
COM16 2014-10-22 1100 1 1
COM16 2014-10-22 1200 6 5

How can I pivot my table to become the result below:

Date Time COM10_Sent_Total COM10_Received_Total COM16_Sent_Total COM16_Received_Total
2014-10-22 1100 1 1 1 1
2014-10-22 1200 4 4 6 5

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-23 : 09:42:06

[code]
DECLARE @t TABLE(Modem_ID char(5),
[Date] date,
[Time] int,
Sent_Total int,
Received_Total int);

INSERT INTO @T(MODEM_ID, [DATE], [TIME], SENT_TOTAL, RECEIVED_TOTAL) VALUES
('COM10','2014-10-22', 1100, 1, 1),
('COM10','2014-10-22', 1200, 4, 4),
('COM16','2014-10-22', 1100, 1, 1),
('COM16','2014-10-22', 1200, 6, 5)

SELECT [DATE], [TIME]
, MAX(CASE WHEN MODEM_ID = 'COM10' THEN SENT_TOTAL END) AS COM10_SENT_TOTAL
, MAX(CASE WHEN MODEM_ID = 'COM10' THEN RECEIVED_TOTAL END) AS COM10_RECEIVED_TOTAL
, MAX(CASE WHEN MODEM_ID = 'COM16' THEN SENT_TOTAL END) AS COM16_SENT_TOTAL
, MAX(CASE WHEN MODEM_ID = 'COM16' THEN RECEIVED_TOTAL END) AS COM16_RECEIVED_TOTAL
FROM @T
GROUP BY [DATE], [TIME]
ORDER BY [DATE], [TIME]
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources