| Author |
 Topic  |
|
|
sureshsmanian
Starting Member
24 Posts |
 Posted - 11/10/2012 : 05:00:59
|
Hi
I have a table TTNew(ID,Employee,WDay,Schedule1,Schedule2,Schedule3)
with the following records:
---------------------------------------------------------
Id Employee WDay Schedule1 Schedule2 Schedule3
---------------------------------------------------------
2 Kiran Sun Read Watch Play
3 Kiran Mon Office Travel Meet
4 Kiran Tue Personal Travel Meet
5 Kiran Wed Board Office Conference
6 Mohd Sun Meet Market Session
7 Mohd Mon Office Market Travel
8 Mohd Tue Travel Market Conference
9 Mohd Wed Personal Meet Watch
---------------------------------------------------------
I need the output in the following manner:
that is, against the employee and day the duties are needed to be grouped.
-----------------------------------------------------------
Sno Employee Sun Mon Tue .....
-----------------------------------------------------------
1 Kiran Read Watch Play | Office Travel Meet | .....
2 Mohd Meet Market Session | Office Market Travel | .....
-- ...
-- ...
------------------------------------------------------------
For your information, query to create table and records
---
CREATE TABLE [dbo].[ttnew](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Employee] [nvarchar](50) NULL,
[WDay] [nvarchar](20) NULL,
[Schedule1] [nvarchar](50) NULL,
[Schedule2] [nvarchar](50) NULL,
[Schedule3] [nvarchar](50) NULL
)
--
insert into ttnew(Employee,Wday,Schedule1,Schedule2,Schedule3)
Select 'Kiran','Sun','Read','Watch','Play'
Union
Select 'Kiran','Mon','Office','Travel','Meet'
Union
Select 'Kiran','Tue','Personal','Travel','Meet'
Union
Select 'Kiran','Wed','Board','Office','Conference'
Union
Select 'Mohd', 'Sun','Meet','Market','Session'
Union
Select 'Mohd', 'Mon','Office','Market','Travel'
Union
Select 'Mohd', 'Tue','Travel','Market','Conference'
Union
Select 'Mohd', 'Wed','Personal','Meet','Watch'
----------------------------
Thanks for your help.
Regards
SSM
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/10/2012 : 07:52:17
|
You can use the PIVOT operator like shown below:SELECT * FROM
(
SELECT
employee,
wday,
schedule1 + ' ' + schedule2 + ' ' + schedule3 AS schedules
FROM
ttnew
)s
PIVOT
(MAX(schedules) FOR wday IN (Sun,Mon,Tue,Wed,Thu,Fri,Sat))P |
 |
|
|
sureshsmanian
Starting Member
24 Posts |
Posted - 11/11/2012 : 02:23:47
|
| Thank you so much. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/11/2012 : 07:46:50
|
| You are very welcome! |
|
|
|
sureshsmanian
Starting Member
24 Posts |
Posted - 11/14/2012 : 03:36:57
|
Hi
I have a modification in my above table, ie) one more field 'Session' has been added.
CREATE TABLE [dbo].[ttnew](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Employee] [nvarchar](50) NULL,
[WDay] [nvarchar](20) NULL,
[Schedule1] [nvarchar](50) NULL,
[Schedule2] [nvarchar](50) NULL,
[Schedule3] [nvarchar](50) NULL,
[Session] [nvarchar] (20) NULL
)
for the same day, schedules will be different for each session (ie, Forenoon session, Afternoon session)
insert into ttnew(Employee,Wday,Schedule1,Schedule2,Schedule3,Session)
Select 'Kiran','Sun','Read','Watch','Play','FN'
Union
Select 'Kiran','Mon','Office','Travel','Meet','FN'
Union
Select 'Kiran','Tue','Personal','Travel','Meet','FN'
Union
Select 'Kiran','Wed','Board','Office','Conference','FN'
Union
Select 'Mohd', 'Sun','Meet','Market','Session','FN'
Union
Select 'Mohd', 'Mon','Office','Market','Travel','FN'
Union
Select 'Mohd', 'Tue','Travel','Market','Conference','FN'
Union
Select 'Mohd', 'Wed','Personal','Meet','Watch','FN'
Union
Select 'Kiran','Sun','Conference','Read','Music','AN'
Union
Select 'Kiran','Mon','Seminar','Play','Read','AN'
Union
Select 'Kiran','Tue','Read','Seminar','Travel','AN'
Union
Select 'Kiran','Wed','Meet','Travel','Play','AN'
Union
Select 'Mohd', 'Sun','Music','Play','Travel','AN'
Union
Select 'Mohd', 'Mon','Travel','Music','Seminar','AN'
Union
Select 'Mohd', 'Tue','Conference','Seminar','Market','FN'
Union
Select 'Mohd', 'Wed','Play','Market','Seminar','FN'
==============================
when I was trying with the above reply, I could able to see only ONE SESSION. whereas I want for the Particular day (from pivot values) but for both the sessions[FN and AN] as two different rows for each employee.
Thanks & Regards
SSM
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/14/2012 : 06:55:22
|
Add Session also into the inner querySELECT * FROM
(
SELECT
employee,
wday,
Session,
schedule1 + ' ' + schedule2 + ' ' + schedule3 AS schedules
FROM
ttnew
)s
PIVOT
(MAX(schedules) FOR wday IN (Sun,Mon,Tue,Wed,Thu,Fri,Sat))P
|
|
|
|
sureshsmanian
Starting Member
24 Posts |
Posted - 11/19/2012 : 04:31:15
|
Thank you so much. Actually, in my real query i was not selecting employee column, so it was not displaying for each session. But now it is ok.
Currently, i have an issue with the pivot values ie) as per the above example the wday (sun, mon, tue...) has to be selected from a table. I have replaced the real values with --- Select wday from weektable -- (a table which has all the values)
but there is an error. Kindly help me in this regard.
Thanks & Regards.
SSM
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
|
| |
Topic |
|
|
|
Sql Cross tab
