SQL Quastion

Good Morning All

i have a Table

Code:

---

===================
DESCR        CYCLE
===================
Rendle C      |        5 |
Reid M Prof  |        9 |
Randall S Ms  |        3 |
Reid M Prof  |        3 |
Rendall G Mr  |        3 |
Rendle C      |        3 |
Rendall G Mr  | 2 |
===================

---

Now as you can see "Rendle C" and "Rendall G Mr" appears Twice, now i want append the cyle field if the descr appears twice, the above list should now look like this

Code:

---

===================
DESCR        CYCLE
===================
Rendle C      |        5 3 |
Reid M Prof  |        9 3 |
Rendall G Mr  |        3 2 |
===================

---

How can i do this in SQl

Thank you

Quote:

---

Originally Posted by vuyiswamb

How can i do this in SQl

---

You cannot - when rows are "moved" to columns it's called cross-tab.
If you're using some report designer like Crystal Reports then most of them offer cros-tab style reports.
Otherwise, you'll need to do that at run time or (if you're using db engine like MS SQL, Oracle, etc) write a function in the database that will output some kind of concatenated cycles (separated by space, comma, etc) based on current desc.
In case of a function your sql may look like ths:

Code:

---

select distinct
    Desc,
    GetAllCycles(Desc)
From
    Cycles
Order By
    Desc

---

What database do you use?

Thank you very much guys for your Help. i was able to do it and i have one Problem., i have duplicates like this

Code:

---

DESCR                CYCLES               
---------------------------------------
Galloway A Ms    20 21 
Gama, E              20 21 
Giatsi M Ms          20 21 
Gibbon F Prof      20 21 
Gibson C Ms        20 21 
Gilfillin, P        20 21 
Galloway A Ms    21 20 
Gama, E              21 20 
Giatsi M Ms          21 20 
Gibbon F Prof      21 20 
Gibson C Ms        21 20 
Gilfillin, P        21 20 

---

Now all this are Correct but i need to get rid of a dupliate. Check the Bolded part

Gilfillin, P 21 20 . i have reproduced this table from the Following

I have a First Stored Procedure doing the Following

Code:

---

ALTER PROC [dbo].[sp_Get_Staff_Cycles]
(
@ACTIVITY_ID INT
)
AS
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]

SELECT  S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN tbl_Cycles_Staff C
ON C.IDL = MTN.ID
WHERE MTN.ACTV = @ACTIVITY_ID

---

and lets Follow the Final table and the following Stored Procedure that does the Following

Code:

---

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
This Sp is the one that Arrange the Cycles from the Grid, the way Paul requested it
if the Grid Has more than one description, append the cyle to another one and remove it
*/

ALTER  PROC [dbo].[sp_Get_Staff_Cycles_internal]

AS
--
 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL_NEW_FINAL]'))
 DROP TABLE FINAL_NEW_FINAL
--ADD THIS TO A NEW TABLE NAMED 'FINAL_NEW_FINAL'
Select t1.DESCR,  STR(t1.CYCLETEMPLATE) + ''+ STR(t2.CYCLETEMPLATE) AS [CYCLES]
INTO FINAL_NEW_FINAL From FINAL t1, FINAL t2
Where t2.DESCR = t1.DESCR
And t2.CYCLETEMPLATE <> t1.CYCLETEMPLATE;

--Get the Missing Records
--INSERT THE ONE THAT WAS MISSING
INSERT INTO FINAL_NEW_FINAL
SELECT DESCR ,CYCLETEMPLATE
FROM FINAL
WHERE DESCR NOT IN (SELECT DESCR FROM FINAL_NEW_FINAL)

-- Final Data for Rendering
SELECT DISTINCT(DESCR),CYCLES FROM  FINAL_NEW_FINAL

---

Now this above sp produce those Duplicates


Thank you

Good Morning Guys i still have a Problem Regarding this Issue, if there are more entry i get the Duplicates problem again, let me explain again with an Example a little similar to the above

Code:

---

ID    ||    DESCR      || CYCLE
==============================
1      ||Earl G        ||  20
2      ||Earl G        ||  21
3      ||Earl G        ||  22
4      ||Davidson I Dr  ||  20
5      ||Davidson I Dr  ||  21
6      ||Davidson I Dr  ||  22
7      ||Easton C      ||  20
8      ||Easton C      ||  21
9      ||Easton C      ||  22
10    ||Edwards J Ms  ||  20
11    ||Edwards J Ms  ||  21
12    ||Edwards J Ms  ||  22

---

As you can see in the Description Field “Earl G” appears 3 times and it can be more than that. Now I want to append Cycles [cycle] strings to the corresponding description, I want to run a query so that this table may look like this

Code:

---

ID      ||    DESCR            || CYCLE
================================================
1        ||        Earl G          ||  20 , 21, 22
4        ||        Davidson I Dr  ||  20 , 21, 22
7        ||        Easton C        ||  20 , 21, 22
10      ||        Edwards J Ms    ||  20 , 21, 22

---

How can I achieve this in SQL


Thank you