[RESOLVED] SQL Quastion

[vuyiswam]

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

[olivthill2]

Try:Select t1.DESCR, t1.CYCLE, t2.CYCLE
From THETABLE t1, THETABLE t2
Where t2.DESCR = t1.DESCR
And t2.CYCLE <> t1.CYCLE

[ComITSolutions]

Select DESCR ,Max(CyCle) ,Min(CyCle)
From TblName Group By DESCR Having Count(DESCR ) >1

This Will Work only when you two rows for a the DESCR. What If the number of rows is more than 2? What is the output requied when more rows?

[vuyiswam]

Good Afternoon

This will be Call


Select t1.DESCR, t1.CYCLETEMPLATE, t2.CYCLETEMPLATE
From FINAL t1, FINAL t2
Where t2.DESCR = t1.DESCR
And t2.CYCLETEMPLATE <> t1.CYCLETEMPLATE


but the catch its when one does not appear more than once , if so i want it to display only cycle that it has

Thanks

[olivthill2]

In that case you need a left join. In Oracle, this can be done with "(+)", giving:
Select t1.DESCR, t1.CYCLETEMPLATE, t2.CYCLETEMPLATE
From FINAL t1, FINAL t2
Where t2.DESCR (+) = t1.DESCR
And t2.CYCLETEMPLATE (+) < t1.CYCLETEMPLATEN.B. I am lso replacing "<>" by "<" in order to avoid having "Rendle C | 5 3 |" and "Rendle C | 3 5 |"

[vuyiswam]

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

[vuyiswam]

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

[vuyiswam]

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

[ComITSolutions]

If you are using MS SQL server. You can write a user defined function to get CYCLE

[vuyiswam]

I kindly need an Example

Thanks

[ComITSolutions]

Create a Function as Below




CREATE FUNCTION GetCycle (@Descr Varchar(50))
RETURNS Varchar(500)
AS
BEGIN
Declare @RetStr as varchar(500),@Cycle Int --<-- Assuming Cycle field is of Type Integer

Declare TmpCur Cursor For select CyCle From YourTblName Where Descr=@Descr
Open TmpCur
Set @RetStr=''
Fetch Next From TmpCur Into @Cycle
While @@Fetch_status=0
Begin
Set @RetStr = @RetStr + Case when @RetStr='' then '' else ' ' End + Cast(@Cycle as varchar)
Fetch Next From TmpCur Into @Cycle
End
Close TmpCur
Deallocate TmpCur
return (@RetStr)
END





You can call this Function as below

Select Descr,.dbo.GetCycle(Descr) As Cycle From YourTableName Where Descr
in ( select Distinct Descr From YourTableName)

[vuyiswam]

Good Afternoon ComITSolutions

Thank you for your Wonderfull Answer. It work like charm.


Thanks