SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 pivot table based on columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cjcclee
Starting Member

33 Posts

Posted - 07/16/2013 :  18:46:32  Show Profile  Send cjcclee an AOL message  Reply with Quote
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

547 Posts

Posted - 07/16/2013 :  20:10:40  Show Profile  Reply with Quote


-- 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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/17/2013 :  04:37:09  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000