September 10, 2007 at 9:49 am
Table : EmpProject
Emp Project
John CCT1
Lee CDT2
Raj CCT1
Kan CDT3
I would like to have results like
CCT1 John,Raj
CDT2 Lee
CDT3 Kan
Thanks in advance.
R
September 10, 2007 at 11:13 am
P, What you want isn't actually a Pivot. Pivot will rotate the values from rows to columns but only if you have a predefined set of KNOWN values in the target columns.
One way to get the results that you want is as follows. KEEP IN MIND that this isn't very flexible, and you'll need to adjust the code to fit your needs.
So this isn't the best solution by any means, but it should be informative.
/* I've created a table called temp to hold your test data */
-- first we create two CTE's "Common Table Expressions" to hold the
-- base table's information with a ROW_NUMBER column added.
-- This will be used to specify the target column of the emp name
WITH
base AS (SELECT project
,emp
-- the row number will start over each time the project changes values
,ROW_NUMBER() OVER (PARTITION BY project ORDER BY project, emp) AS rn
FROM dbo.temp),
-- now we "move" the row's value from the first column to it's resting column
pivoted AS (SELECT project
,r1 = (CASE WHEN rn = 1 THEN ISNULL(emp + ', ', '') ELSE '' END)
,r2 = (CASE WHEN rn = 2 THEN ISNULL(emp + ', ', '') ELSE '' END)
,r3 = (CASE WHEN rn = 3 THEN ISNULL(emp + ', ', '') ELSE '' END)
,r4 = (CASE WHEN rn = 4 THEN ISNULL(emp + ', ', '') ELSE '' END)
--,r. = (CASE ......) add more as needed
FROM base)
-- now max them all together to get rid of spaces
SELECT
project
,emp =
MAX(r1) +
MAX(r2) +
MAX(r3) +
MAX(r4)
--MAX(r.) + add more as needed
FROM
pivoted
GROUP BY
project
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 10, 2007 at 11:18 am
Thanks Jason, That works greatly!
R
September 11, 2007 at 6:09 am
Exactly. However to see what A Pivot of that data would give you;
DROP
TABLE #a
CREATE TABLE #a
(emp VARCHAR(20),project VARCHAR(20))
INSERT INTO #a
VALUES('John','CCT1')
INSERT
INTO #a
VALUES('Lee','CDT2')
INSERT
INTO #a
VALUES('Raj','CCT1')
INSERT
INTO #a
VALUES('Kan','CDT3')
SELECT
* FROM #a
SELECT
*
FROM
(SELECT * FROM #a) AS A
PIVOT
(
COUNT(Project)
FOR Project IN ([CCT1],[CDT2],[CDT3])
) AS B
| EMP | CCT1 | CDT2 | CDT3 |
| John | 1 | 0 | 0 |
| Kan | 0 | 0 | 1 |
| Lee | 0 | 1 | 0 |
| Raj | 1 | 0 | 0 |
September 11, 2007 at 4:31 pm
I just posted a question re SybaseSQLAny functions missing in SS
The SybaseSQLAny List() function comes to mind here.
"select Project , list(Emp) as "Emplist" from EmpProject order by Project"
would produce the exact results you are asking for, P:
Project Emplist
CCT1 John,Raj
CDT2 Lee
CDT3 Kan
My migration projects is going to have to deal wth this conversion as well. The LIST() function has been very useful, and I am sorry to see it go.
It seems that SQL Server is making things hard on us. Is there some standard that MS is clinging to that takes these sort of functions out of the picture. Is there a repository of user defined functions that we can draw from?
My boss is decrying the shift to SQL Server from SybaseSQLAny (client requirement.) At first I paid no attention to him, but now that I am eblow deep in migration project I am beginning to feel otherwise
Joel
Takauma
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply