SQL Quastion

[vuyiswamb]

Good Morning All

i have a Table

===================
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

===================
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

[RhinoBull]

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:

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

What database do you use?

[vuyiswamb]

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

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

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

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

[vuyiswamb]

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

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

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