Click here to Skip to main content
15,304,276 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I am having a table like this :
SNO EmpCode Name    Day OT_Day  Date                WorkingHours    OT Hours
1   338     A       1   1_OT    Jul  1 2013 12:00AM 8:51            1:15
2   338     A       2   2_OT    Jul  2 2013 12:00AM 9:14            1:15
3   338     A       3   3_OT    Jul  3 2013 12:00AM 8:51            1:21
4   338     A       4   4_OT    Jul  4 2013 12:00AM 8:44            1:16
5   338     A       5   5_OT    Jul  5 2013 12:00AM 8:42            1:15
6   338     A       6   6_OT    Jul  6 2013 12:00AM 2:07            1:16



Now using pivoting I want to generate the output like this :
EmpCode Name 1     1_OT  2    2_OT  3    3_OT  4    4_OT...................
338     A    8:51  1:15  9:14 1:15  8:51 1:21  8:44 1:16...................


That mean I would like to pivot two columns Day and OT_Day.

Thanks in advance.
Please help...............
Posted
Updated 28-Jul-13 10:49am
v2

1 solution

Try this:
SQL
CREATE TABLE #atable (SNO INT, EmpCode VARCHAR(30), [Name] VARCHAR(5), [Day] INT, OT_Day VARCHAR(30), Date DATETIME, WorkingHours VARCHAR(30), [OT Hours] VARCHAR(30))

INSERT INTO #atable (SNO, EmpCode, [Name], [Day], [OT_Day], Date, WorkingHours, [OT Hours])
SELECT 1, 338, 'A', 1, '1_OT', 'Jul 1 2013 12:00AM',  '8:51', '1:15'
UNION ALL SELECT 2, 338, 'A', 2, '2_OT', 'Jul  2 2013 12:00AM', '9:14', '1:15'
UNION ALL SELECT 3, 338, 'A', 3, '3_OT', 'Jul  3 2013 12:00AM', '8:51', '1:21'
UNION ALL SELECT 4, 338, 'A', 4, '4_OT', 'Jul  4 2013 12:00AM', '8:44', '1:16'
UNION ALL SELECT 5, 338, 'A', 5, '5_OT', 'Jul  5 2013 12:00AM', '8:42', '1:15'
UNION ALL SELECT 6, 338, 'A', 6, '6_OT', 'Jul  6 2013 12:00AM', '2:07', '1:16'
--UNION ALL SELECT 10, 338, 'A', 10, '10_OT', 'Jul  10 2013 12:00AM', '9:07', '0:59'

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + [aDay]
					FROM (
							SELECT CONVERT(VARCHAR(10), [Day]) AS [aDay]
							FROM #atable
							UNION ALL
							SELECT CONVERT(VARCHAR(10), [OT_Day]) AS [aDay]
							FROM #atable
						) AS A
					--ORDER BY '],[' + [aDay]
			FOR XML PATH('')),1,2,'') + ']'

--SELECT @cols 

SET @dt = N'SELECT EmpCode, [Name], CONVERT(VARCHAR(10),[Day]) AS [aDay], WorkingHours AS [Hrs]
		FROM #atable
		UNION ALL
		SELECT EmpCode, [Name], [OT_Day] AS [aDay], [OT Hours] AS [Hrs]
		FROM #atable'
--EXEC(@dt)

SET @pt = N'SELECT EmpCode, [Name], ' + @cols + ' ' +
			'FROM (' + @dt + ') AS DT ' + 
			'PIVOT(MAX(Hrs) FOR [aDay] IN(' + @cols + ')) AS PT '
EXEC(@pt)

DROP TABLE #atable


Result: as expected ;)
   
Comments
Raja Sekhar S 29-Jul-13 3:33am
   
Nice one... +5!
Maciej Los 29-Jul-13 3:39am
   
Thank you, Raja ;)
Raja Sekhar S 29-Jul-13 3:58am
   
You are Welcome...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900