unique record question - 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 2005 Forums

Transact-SQL (2005)

unique record question

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-11-18 : 14:12:41

I have a table which looks like this
Account EventID Time
123 x 11/18/08 7:30AM
123 y 11/18/08 7:45AM
123 z 11/18/08 7:59AM
456 x 11/18/08 7:45AM
456 z 11/18/08 7:59AM

i want the out put in this fashion
Account X Y Z
123 11/18/08 7:30AM 11/18/08 7:45AM 11/18/08 7:59AM
456 11/18/08 7:45AM 11/18/08 7:59AM

How can i achieve this.
Thanks in advance for everyone.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-18 : 14:39:09

SELECT account, [x] AS x, [y] AS y,[z] AS z
FROM
(SELECT account, EventID, Time
FROM yourtable ) tb
PIVOT
(
Max (Time)
FOR account IN
( [x], [y],[z])
) AS pvt

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-11-18 : 16:23:17

Thanks lionofdezert; but i am getting an error incorrect syntact near PIVOT i think it is beacuse tb( what does this refer to).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-18 : 17:18:27

[code]DECLARE @Sample TABLE
(
Account INT,
EventID CHAR(1),
Time DATETIME
)

INSERT @Sample
SELECT 123, 'x', '11/18/08 7:30AM' UNION ALL
SELECT 123, 'y', '11/18/08 7:45AM' UNION ALL
SELECT 123, 'z', '11/18/08 7:59AM' UNION ALL
SELECT 456, 'x', '11/18/08 7:45AM' UNION ALL
SELECT 456, 'z', '11/18/08 7:59AM'

SELECT p.Account,
p.x,
p.y,
p.z
FROM @Sample AS s
PIVOT (
MAX(Time)
FOR EventID IN ([x], [y], [z])
) AS p[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources