15,304,276 members
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.

Posted
Updated 28-Jul-13 10:49am
v2

## Solution 1

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 (
FROM #atable
UNION ALL
FROM #atable
) AS A
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 ;)
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...