Splitting the values into two columns

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

Splitting the values into two columns

Author

Topic

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-05 : 07:45:16

Hi
I have three tables: Meeting, Department and MeetingEmployee

Meeting (Id, MeetingId - PK, Description)
Department (Id, DeptId - PK, Description)
MeetingEmployee (Id, EmpId, DeptId, MeetingId)

I would require to get the output

Description SalesEmpId Mkt-EmpId Purchase-EmpId
------------ ------------ ----------- ---------------
First Meeting 1 4 --
First meeting 2 5 --

Second Meeting 2 4 --
Second Meeting 1 -- ---

--------------------------------------------------------------

I have attached the DDLs and DML for your reference.

Thanks for your help.

Rgds
SSM
---------------------------------
----------------
CREATE TABLE [dbo].[Meeting](
[Id] [int] NOT NULL,
[meetingId] [nvarchar](20) NOT NULL,
[description] [nvarchar](20) NULL,

CONSTRAINT [pk_meeting] PRIMARY KEY CLUSTERED
(
[meetingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

----
CREATE TABLE [dbo].[Department](
[Id] [int] NOT NULL,
[DeparmentId] [int NOT NULL,
[description] [nvarchar](20) NULL,

CONSTRAINT [pk_department] PRIMARY KEY CLUSTERED
(
[DeparmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

----

CREATE TABLE [dbo].[MeetingEmployee](
[meetingempId] [nvarchar](20) NOT NULL,
[empId] [int] NULL,
[departmentid] [int] NOT NULL,
[meetingid] [nvarchar](20) NOT NULL,
CONSTRAINT [pk_meetingemployee] PRIMARY KEY CLUSTERED
(
[meetingempId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MeetingEmployee] WITH CHECK ADD CONSTRAINT [FK_MeetingEmployee_Department] FOREIGN KEY([departmentid])
REFERENCES [dbo].[Department] ([departmentid])
GO

ALTER TABLE [dbo].[MeetingEmployee] CHECK CONSTRAINT [FK_MeetingEmployee_Department]
GO

ALTER TABLE [dbo].[MeetingEmployee] WITH CHECK ADD CONSTRAINT [FK_MeetingEmployee_Meeting] FOREIGN KEY([meetingid])
REFERENCES [dbo].[Meeting] ([meetingId])
GO

ALTER TABLE [dbo].[MeetingEmployee] CHECK CONSTRAINT [FK_MeetingEmployee_Meeting]
GO
-----------------------------
insert into Meeting values (1,'M001','First');
insert into Meeting values (2,'M002','Second');
insert into Meeting values (3,'M003','Third');
---
insert into Department values (1,1,'Sales');
insert into Department values (2,2,'Marketing');
insert into Department values (3,3,'Purchase');
----

insert into MeetingEmployee values(1,1,1,'M001');
insert into MeetingEmployee values(2,2,1,'M001');
insert into MeetingEmployee values(3,4,2,'M001');
insert into MeetingEmployee values(4,5,2,'M001');
insert into MeetingEmployee values(5,2,1,'M002');
insert into MeetingEmployee values(6,4,2,'M002');
insert into MeetingEmployee values(7,1,1,'M002');
--------

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-06 : 04:51:59

It would be something like this but it looks like you want to have multiple rows per meeting.
Is there any way of deciding which empids are in each row?

select m.description,
SalesEmpId = max(case when d.description = 'Sales' then me.EmpId end,
[Mkt-EmpId] = max(case when d.description = 'Marketing' then me.EmpId end,
[Purchase-EmpId] = max(case when d.description = 'Purchase' then me.EmpId end
from MeetingEmployee me
join Meeting m
on m.MeetingId = me.MeetingId
join Department d
on d.DepartmentId = me.MeetingId
group by m.description

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-06 : 05:00:02

Maybe something like (untexted)

with cte as
(
select m.description, Type = d.description, me.EmpId
from MeetingEmployee me
join Meeting m
on m.MeetingId = me.MeetingId
join Department d
on d.DepartmentId = me.MeetingId
) ,
cte2 as
(
select description, EmpId, Type, seq = row_number() over (partition by description, type order by empid) from cte
)
select c.description, csales.EmpId, cMarketing.EmpId, cPurchase.EmpId
from (select distinct description, seq from cte2) c
left join cte2 csales
on c.description = csales.description
and csales.Type = 'Sales'
and c.seq = csales.seq
left join cte2 csales
on c.description = cMarketing.description
and csales.Type = 'Marketing'
and c.seq = cMarketing.seq
left join cte2 cPurchase
on c.description = cPurchase.description
and csales.Type = 'Purchase'
and c.seq = cPurchase.seq

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-07 : 03:38:00

Hi nigelrivett,
Thanks for your reply. Your first answer seems to be returning the rows, however it returns only one row ( i think the max employee id row), whereas it should return more than one row, as per the logic, more than one employee from a department will attend the same meeting right (that is subquery has to return more number of rows). But your query returns only one row.

------------------
select m.description,
SalesEmpId = max(case when d.description = 'Sales' then me.EmpId end,
[Mkt-EmpId] = max(case when d.description = 'Marketing' then me.EmpId end,
[Purchase-EmpId] = max(case when d.description = 'Purchase' then me.EmpId end
from MeetingEmployee me
join Meeting m
on m.MeetingId = me.MeetingId
join Department d
on d.DepartmentId = me.departmentId
group by m.description
---------------

Can you guide me anyother way i can do it in report builder (filtering rows for column A (Sales department employees attended the first meeting) , filtering rows for a column B (Marketing employees attended the firstmeeting and so on..).

Regards
SSM.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 04:16:50

Did you have a look at the second one?
The problem is that there is no way from the data of alocating an employee to a row so you need to create a value - hence the row_number()

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 04:26:34

How about this - I've corrected a few bugs in the code.

declare @Meeting TABLE
(
[Id] [int] NOT NULL,
[meetingId] [nvarchar](20) NOT NULL,
[description] [nvarchar](20) NULL
)

declare @Department TABLE
(
[Id] [int] NOT NULL,
[DepartmentId] int NOT NULL,
[description] [nvarchar](20) NULL
)

declare @MeetingEmployee TABLE
(
[meetingempId] [nvarchar](20) NOT NULL,
[empId] [int] NULL,
[departmentid] [int] NOT NULL,
[meetingid] [nvarchar](20) NOT NULL
)

-----------------------------
insert into @Meeting values (1,'M001','First');
insert into @Meeting values (2,'M002','Second');
insert into @Meeting values (3,'M003','Third');
---
insert into @Department values (1,1,'Sales');
insert into @Department values (2,2,'Marketing');
insert into @Department values (3,3,'Purchase');
----

insert into @MeetingEmployee values(1,1,1,'M001');
insert into @MeetingEmployee values(2,2,1,'M001');
insert into @MeetingEmployee values(3,4,2,'M001');
insert into @MeetingEmployee values(4,5,2,'M001');
insert into @MeetingEmployee values(5,2,1,'M002');
insert into @MeetingEmployee values(6,4,2,'M002');
insert into @MeetingEmployee values(7,1,1,'M002');
--------

select m.description, Type = d.description, me.EmpId
from @MeetingEmployee me
join @Meeting m
on m.MeetingId = me.MeetingId
join @Department d
on d.DepartmentId = me.DepartmentId

;with cte as
(
select m.description, Type = d.description, me.EmpId
from @MeetingEmployee me
join @Meeting m
on m.MeetingId = me.MeetingId
join @Department d
on d.DepartmentId = me.DepartmentId
) ,
cte2 as
(
select description, EmpId, Type, seq = row_number() over (partition by description, type order by empid) from cte
)
select c.description, salesemp = csales.EmpId, marketingemp=cMarketing.EmpId, purchaseemp=cPurchase.EmpId
from (select distinct description, seq from cte2) c
left join cte2 csales
on c.description = csales.description
and csales.Type = 'Sales'
and c.seq = csales.seq
left join cte2 cMarketing
on c.description = cMarketing.description
and cMarketing.Type = 'Marketing'
and c.seq = cMarketing.seq
left join cte2 cPurchase
on c.description = cPurchase.description
and cPurchase.Type = 'Purchase'
and c.seq = cPurchase.seq

description salesemp marketingemp purchaseemp
-------------------- ----------- ------------ -----------
First 1 4 NULL
First 2 5 NULL
Second 1 4 NULL
Second 2 NULL NULL

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-07 : 04:47:14

Hi,
Thanks for your reply.
But the second query (modified one) returns the rows in this format.

Description Type EmpId
------------------------
First Sales 1
First Sales 2
First Market 4
First Market 5
Second Sales 2
Second Market 4
Second Sales 1

Actually i want under the departmentname employee ids have to display.
--------------
This is the query i have copied from yourside.

select m.description, Type = d.description, me.EmpId
from MeetingEmployee me
join Meeting m
on m.MeetingId = me.MeetingId
join Department d
on d.DepartmentId = me.DepartmentId

;with cte as
(
select m.description, Type = d.description, me.EmpId
from MeetingEmployee me
join Meeting m
on m.MeetingId = me.MeetingId
join Department d
on d.DepartmentId = me.DepartmentId
) ,
cte2 as
(
select description, EmpId, Type, seq = row_number() over (partition by description, type order by empid) from cte
)
select c.description, salesemp = csales.EmpId, marketingemp=cMarketing.EmpId, purchaseemp=cPurchase.EmpId
from (select distinct description, seq from cte2) c
left join cte2 csales
on c.description = csales.description
and csales.Type = 'Sales'
and c.seq = csales.seq
left join cte2 cMarketing
on c.description = cMarketing.description
and cMarketing.Type = 'Marketing'
and c.seq = cMarketing.seq
left join cte2 cPurchase
on c.description = cPurchase.description
and cPurchase.Type = 'Purchase'
and c.seq = cPurchase.seq

------------------

Rgds
SSM

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 06:05:34

You shold get two resultsets from that - the second one is the one you want.
You can remove the first select - it's just for testing.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-07 : 06:21:00

Hi Nigelrivett,
Thank you so much for your help. If you could explain this query it would be helpful for me to understand. Moreover I want to replace the EmpIds with the corresponding employeenames which I would join and access from an another table StaffDetails. And if you could guide me, is there any way I can do this stuff in report builder using filtering the columns according to the conditions!!

Regards
SSM.

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-07 : 06:29:53

Hi Nigelrivett,
Thankyou, I have combined with staff details as well to display name, now it is working perfect.

Regards
SSM

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 07:03:43

;with cte as
(
select m.description, Type = d.description, me.EmpId
from MeetingEmployee me
join Meeting m
on m.MeetingId = me.MeetingId
join Department d
on d.DepartmentId = me.DepartmentId
) ,
cte2 as
(
select t.description, t.EmpId, sd.employeename, t.Type, seq = row_number() over (partition by description, type order by empid)
from cte t
join StaffDetails sd
on t.empid = sd.empid
)
select c.description, salesemp = csales.employeename, marketingemp=cMarketing.employeename, purchaseemp=cPurchase.employeename
from (select distinct description, seq from cte2) c
left join cte2 csales
on c.description = csales.description
and csales.Type = 'Sales'
and c.seq = csales.seq
left join cte2 cMarketing
on c.description = cMarketing.description
and cMarketing.Type = 'Marketing'
and c.seq = cMarketing.seq
left join cte2 cPurchase
on c.description = cPurchase.description
and cPurchase.Type = 'Purchase'
and c.seq = cPurchase.seq

Not sure about reportbuilder - I'll post some infor about what this is doing in a short while

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 09:51:25

The first cte gets a list of the meetings and empid with their types
The second cte (cte2) assigns a sequence number to each entry by meeting id and type - also gets the names
The final select joins them together using the type and sequence
c is the distinct meeting/seq which is used to gather together all the other employees using the sequence number.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-08 : 03:21:05

Thanks Nigel.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources