-
Table Disposition
Hello Guys!
I have a tabe with 2 fields
One Two in that format
One Two
ACSEK AC01
ACSEK AC02
MOPE AC01
MOPE ACME
MOPE AC04
I would like to select the data so that in the display it comes up like this
ACSEK AC01, AC02
MOPE AC01, ACME, AC04
using a select statement
Any ideas?
-
If you are you using SQL you may be able to write a stored
procedure to do it. Perhaps a cursor for the outter query and
pass the distinct One values to the inner query along with the
count of Twos. The inner query will construct the field Two
containing x number of entries separated by commas. I don't
know if I am explaining this correctly. :confused:
I know you could easily do it in VB. Would that be an option?
-
You didn't say what you have the db in - Access has a crosstab query type, which might do what you want...
Vince
-
I have checked out the crosstab query before I posted. It will not do what Lafor wants. :(
Although it does get close.
-
....
Thanks Guys...
I am using SQL Server....
-
I am at work now and we have SQL 2000 so I will try to create an
example for you when I get a chance.
-
Thank you in advance
-
Did I help you with something before about a year or two ago???
I kind of remember your name. I think it may have been with Outlook?
-
Indeed
You have... it was about 3 months ago...
Thanks again
-
Not a problem. Glad to help.
-
Lafor, this will work if executed from query analyzer.
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LAFOR]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LAFOR]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [DBO].[LAFOR] AS
--INITIALIZE CURSOR
DECLARE @Kount AS NVARCHAR(10), @One AS NVARCHAR(10), @Two2 AS NVARCHAR(50),
@One2 AS NVARCHAR(50), @Two AS NVARCHAR(10), @Temp AS NVARCHAR(50), @KOUNTTEMP AS INTEGER,
@Temp2 AS NVARCHAR(50)
DECLARE rrCursor SCROLL CURSOR FOR
SELECT
[T1].One,
CAST(COUNT([T1].Two) AS NVARCHAR(10)) AS CountOfTwo
FROM
[Table1] AS T1
GROUP BY
[T1].One
OPEN rrCursor
FETCH FIRST FROM rrCursor
INTO @One, @Kount
--INITIALIZE VARS
SET @Temp = ''
SET @Temp2 = ''
SET @KOUNTTEMP = 1
--START LOOP
PRINT 'BEGIN'
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE rrCursor2 SCROLL CURSOR FOR
SELECT
[T1].One,
[T1].Two
FROM
[Table1] AS T1
WHERE [T1].One = @One
OPEN rrCursor2
FETCH FIRST FROM rrCursor2
INTO @One2, @Two2
WHILE @KOUNTTEMP < @Kount + 1
BEGIN
SELECT @Temp = @Temp + @Two2 + ', '
SET @KOUNTTEMP = @KOUNTTEMP + 1
SET @Temp2 = @Temp
FETCH NEXT FROM rrCursor2 INTO @One2, @Two2
END
PRINT @One + ', ' + SUBSTRING(@Temp2,1,LEN(@Temp2)-1)
SET @Temp = ''
SET @Temp2 = ''
SET @KOUNTTEMP = 1
FETCH NEXT FROM rrCursor INTO @One, @Kount
CLOSE rrCursor2
DEALLOCATE rrCursor2
END
PRINT 'END'
--DONE
CLOSE rrCursor
DEALLOCATE rrCursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
'THEN EXECUTE THE SP
EXECUTE Lafor
'RESULTS
BEGIN
ACSEK, AC01, AC02
MOPE, AC01, ACME, AC04
END
I hope this is what you were looking for.
-
Thanks
...Saw you response.... I have been aaccidentally away...
Thanks much...
Thank you
-
No problem. Glad to help.
You can add to it to make it return a cursor.
Not too much harder, but I haven't done that before.
Later.