pivot table based on columns - 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 based on columns

Author

Topic

cjcclee
Starting Member

33 Posts

Posted - 2013-07-16 : 18:46:32

I am new to use pivot table, try to do this

orginial table:

LOG_ID type time
1 IN time1
1 Out time2
1 In time3
1 Out time4
2 In time5
2 Out Time6

In and out are pair value, each logID may have multiple In and OUT, I want result display like

LOG_ID InTime_1 OutTime_1 InTime_2 OutTime_2
1 time1 time2 time3 time4
2 time5 time6 null null

I tried pivot, but can not figure out. Thanks for your help.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-16 : 20:10:40

[CODE]

-- TEST DATA
DECLARE @Temp TABLE(LOG_ID INT, type VARCHAR(4), [time] VARCHAR(10));
INSERT INTO @Temp VALUES
(1, 'IN', 'time1'),
(1, 'OUT', 'time2'),
(1, 'IN', 'time3'),
(1, 'OUT', 'time4'),
(2, 'IN', 'time5'),
(2, 'OUT', 'Time6');

; WITH CTE AS
(SELECT LOG_ID, 'Time_'+type+'_'+CAST(ROW_NUMBER() OVER(PARTITION BY LOG_ID, type
ORDER BY [time]) as VARCHAR) as type, [time] from @Temp)
SELECT * FROM CTE
PIVOT(MAX([time]) FOR type IN([Time_IN_1], [Time_OUT_1], [Time_IN_2], [Time_OUT_2])) as P;[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 04:37:09

[code]
SELECT LOG_ID,
MAX(CASE WHEN Seq=1 THEN AND Type = 'IN' THEN time) AS Time_IN_1,
MAX(CASE WHEN Seq=1 THEN AND Type = 'OUT' THEN time) AS Time_OUT_1,
MAX(CASE WHEN Seq=2 THEN AND Type = 'IN' THEN time) AS Time_IN_2,
MAX(CASE WHEN Seq=2 THEN AND Type = 'OUT' THEN time) AS Time_OUT_2
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LOG_ID,type ORDER BY time) AS Seq,
*
FROM Table
)t
GROUP BY LOG_ID
[/code]

------------------------------------------------------------------------------------------------------
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