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
 General SQL Server Forums
 New to SQL Server Programming
 Sql Cross tab
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sureshsmanian
Starting Member

31 Posts

Posted - 11/10/2012 :  05:00:59  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/10/2012 :  07:52:17  Show Profile  Reply with Quote
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
Go to Top of Page

sureshsmanian
Starting Member

31 Posts

Posted - 11/11/2012 :  02:23:47  Show Profile  Reply with Quote
Thank you so much.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/11/2012 :  07:46:50  Show Profile  Reply with Quote
You are very welcome!
Go to Top of Page

sureshsmanian
Starting Member

31 Posts

Posted - 11/14/2012 :  03:36:57  Show Profile  Reply with Quote
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





Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  06:55:22  Show Profile  Reply with Quote
Add Session also into the inner query
SELECT * 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
Go to Top of Page

sureshsmanian
Starting Member

31 Posts

Posted - 11/19/2012 :  04:31:15  Show Profile  Reply with Quote
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

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  06:57:26  Show Profile  Reply with Quote
The PIVOT operator does not allow selecting the column names from a table. If you don't know the column names in advance, the alternative is to use a dynamic query. There is code and examples on Madhivanan's blog here: http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx
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.08 seconds. Powered By: Snitz Forums 2000