Table Disposition

[Lafor]

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?

[RobDog888]

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.

I know you could easily do it in VB. Would that be an option?

[Ecniv]

You didn't say what you have the db in - Access has a crosstab query type, which might do what you want...


Vince

[RobDog888]

I have checked out the crosstab query before I posted. It will not do what Lafor wants.
Although it does get close.

[Lafor]

Thanks Guys...

I am using SQL Server....

[RobDog888]

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.

[Lafor]

Thanks

[RobDog888]

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?

[Lafor]

You have... it was about 3 months ago...

Thanks again

[RobDog888]

Not a problem. Glad to help.

[RobDog888]

Lafor, this will work if executed from query analyzer.

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.

[Lafor]

...Saw you response.... I have been aaccidentally away...

Thanks much...

Thank you

[RobDog888]

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.