March 22, 2011 at 7:56 am
This select statement:
SELECT
GDetails.ShortName AS [Project],
dbo.fnGetUser(GPersonnelRole.UserID) AS [User],
tblRole.Name AS [Role]
FROM GPersonnelRole
INNER JOIN tblRole On GPersonnelRole.RoleID = tblRole.RoleID
INNER JOIN GDetails ON GPersonnelRole.GID = GDetails.GID
produces data like:
Project User Role
Project 1 Bill Project Leader
Project 1 Jim Executive Lead
Project 1 Mary Chair
Project 2 Arthur Project Leader
Project 2 Frank Executive Lead
Project 2 Linda Chair
How can I rearrange that data so it looks like:
Project 1 Project 2
Project Leader Bill Arthur
Executive Lead Jim Frank
Chair Mary Linda
I've read up on cross tab queries and pivot tables - but it's making no sense I'm afraid.
Thanks for any help
March 22, 2011 at 9:10 am
PIVOTs and cross-tabs change rows to columns. What you want is UNPIVOT, i.e. to change columns to rows.
http://msdn.microsoft.com/en-us/library/ms177410.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2011 at 11:02 am
He definately wants to use Pivot here...
CREATE TABLE #test(
Project VARCHAR(20),
Person VARCHAR(50),
projRole VARCHAR(50))
INSERT #test
( Project, Person, projRole )
VALUES
('Project 1','Bill','Project Leader'),
('Project 1','Jim','Executive Lead'),
('Project 1','Mary','Chair'),
('Project 2','Arthur','Project Leader'),
('Project 2','Frank','Executive Lead'),
('Project 2','Linda','Chair')
SELECT projrole,[Project 1] AS Project1,[Project 2] AS Project2
FROM
(SELECT
Project,
Person,
Projrole
FROM #test) t PIVOT(
MAX(Person)
FOR Project IN ([Project 1],[Project 2])
) AS pvt
ORDER BY Projrole desc
March 22, 2011 at 11:14 am
hi,
Have a look at this
declare @SomeTable1 table (Project varchar(200),users varchar(30), Roles varchar(200))
insert into @SomeTable1 values
('Project 1' , 'Bill', 'Project Leader'),
('Project 1 ', 'Jim ', 'Executive Lead'),
('Project 1 ', 'Mary ', 'Chair'),
('Project 2 ', 'Arthur' ,'Project Leader'),
('Project 2 ', 'Frank ','Executive Lead'),
('Project 2 ', 'Linda ', 'Chair')
--Select * from @SomeTable1 where Roles='Project Leader'
select [Project 1],[Project 2], Roles
FROM (SELECT Project,Roles,users FROM @SomeTable1 ) AS src
PIVOT (MIN(users) FOR Project IN ([Project 1],[Project 2])) AS pvt
ORDER BY Roles desc
Thanks
Parthi
March 22, 2011 at 11:17 am
EDIT: Sorry - other posts appeared while I was writing this - please ignore.
Thanks for your reply and the link. I'm sorry, but I still don't get it.
CREATE TABLE #SomeTable1
(
Project varchar(50),
UserName varchar(50),
UserRole varchar(50)
)
GO
INSERT INTO #SomeTable1
(Project, UserName, UserRole)
SELECT 'Project 1', 'Bill', 'Project Leader' UNION ALL
SELECT 'Project 1', 'Jim', 'Executive Lead' UNION ALL
SELECT 'Project 1', 'Mary', 'Chair' UNION ALL
SELECT 'Project 2', 'Arthur', 'Project Leader' UNION ALL
SELECT 'Project 2', 'Frank', 'Executive Lead' UNION ALL
SELECT 'Project 2', 'Linda', 'Chair'
GO
SELECT * FROM #SomeTable1
The code above produces 6 rows - 3 for Project 1 and 3 for Project 2
But I need to return the data so that the columns are the Projects (in this case one column for Project 1 and one for Project 2) and the rows are the UserRoles and the intersection between the columns and rows contains the UserName for the person who is fulfilling that role for that project. (Or, it could be the Roles are across the top and the Projects are roles - either way would do.
Thanks again.
March 22, 2011 at 11:37 am
Thanks folks - 2 solutions that do the job. All I need to do now is try to understand!
One thing I definitely don't get is ... the example table just has rows for Project 1 and Project 2. In the code provided ...
select [Project 1],[Project 2], Roles
FROM (SELECT Project,Roles,users FROM @SomeTable1 ) AS src
PIVOT (MIN(users) FOR Project IN ([Project 1],[Project 2])) AS pvt
ORDER BY Roles desc
... are [Project 1] and [Project 2] hard-coded?
In the real data there might be any number of projects returned - up to 20 - maybe more.
How is that written if you don't know how many projects are being returned?
Thanks again.
March 22, 2011 at 11:54 am
mtassin (3/22/2011)
He definately wants to use Pivot here...
Sorry, I misunderstood the original intent behind the question.
sku370870 (3/22/2011)
... are [Project 1] and [Project 2] hard-coded?
In the real data there might be any number of projects returned - up to 20 - maybe more.
How is that written if you don't know how many projects are being returned?
What you want is a dynamic cross-tab. Here is a comprehensive article about how to do that...and why cross-tabs almost always outperform the built-in PIVOT operator:
http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2011 at 12:59 pm
opc.three (3/22/2011)
What you want is a dynamic cross-tab. Here is a comprehensive article about how to do that...and why cross-tabs almost always outperform the built-in PIVOT operator:http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]
Jeff writes great articles, though to me, once I want something more than what Pivot wants my approach would be to feed that data into a BI tool (SSRS does good) and make it do the cross-tab. 🙂
March 22, 2011 at 1:05 pm
mtassin (3/22/2011)
opc.three (3/22/2011)
What you want is a dynamic cross-tab. Here is a comprehensive article about how to do that...and why cross-tabs almost always outperform the built-in PIVOT operator:http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]
Jeff writes great articles, though to me, once I want something more than what Pivot wants my approach would be to feed that data into a BI tool (SSRS does good) and make it do the cross-tab. 🙂
I agree, Jeff is a superior asset to the SQL community. Regarding pushing the boundaries of pivot, I have similar thoughts towards the way he solved the "running totals" in T-SQL using quirky updates...I would rather do that in an app tier...but sometimes we're boxed into using T-SQL to do things we would rather do elsewhere so it's nice to know we have some options 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply