Sql Cross tab - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql Cross tab

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2012-11-10 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-10 : 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

31 Posts

Posted - 2012-11-11 : 02:23:47

Thank you so much.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-11 : 07:46:50

You are very welcome!

sureshsmanian
Starting Member

31 Posts

Posted - 2012-11-14 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 06:55:22

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

sureshsmanian
Starting Member

31 Posts

Posted - 2012-11-19 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 06:57:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources