Would this be an appropriate situations for a cursor?

Question

I have the following script:

SELECT left(SHI.FSOKEY, 6) AS [SoNo]
,      substring(SHI.FSOKEY, 7, 3) AS [So Item]
,      right(SHI.FSOKEY, 3) AS [So Rels]
,      QAL.FCLOT AS [LotSerial]

FROM shmast SHM
     INNER JOIN shitem SHI
     ON SHM.FSHIPNO = SHI.FSHIPNO
     INNER JOIN qalotc QAL
     ON SHM.FSHIPNO = Left(QAL.FCUSEINDOC, 6)
        AND substring(QAL.FCUSEINDOC, 7, 6) = SHI.FITEMNO

This produces output that looks like this:

SoNo      So Item  SoRels  LotSerial
123456      1      001     ABCD
123456      1      001     AMOH
123456      1      001     POWK
123456      1      001     IUIL
123456      1      002     ABCE

I want to group by SoNo, SoItem, SoRels and get a list of LotSerials for each. So, my output would look like this:

SoNo      So Item  SoRels  LotSerial
123456      1      001     ABCD, AMOH, POWK, IUIL
123456      1      002     ABCE

I need to do so that I can pull this information back into a main query based on the SoNo, SoItem, SoRels.

Any help would be greatly appreciated.

Answer 1

As always, avoid cursors whenever possible. Your scenario would be a good fit for a user defined function. I simplified your schema a little for this example. Essentially we are concatenating the serials that match with commas to a variable within a user defined function, and then retuning the result. If Null values are possible, you might want to add usage of Coalesce

create table SO (SONO int)
insert into SO values (1)
insert into SO values (2)
insert into SO values (3)


create table SOCHILD 
(SONO int, SerialNo varchar(10))
insert into SOCHILD values (1, 'ABCD')
insert into SOCHILD values (1, 'EFGH')
insert into SOCHILD values (1, 'IJKL')
GO
create function fx_GetSerials(@SONO int)
returns varchar(1000) as
Begin
    Declare @ret varchar(1000)
    set @ret = ''
    Select @ret = @ret + SerialNo + ','
    from SOCHILD where SONO = @SONO
    if (len(@ret) > 0) 
     set @Ret = left(@ret, len(@ret) -1)
    return @ret 
End 
GO
select dbo.Fx_GetSerials(1)
drop function fx_GetSerials 
Drop table SO
Drop table SOCHILD

Results ABCD,EFGH,IJKL

Answer 2

@cmsjr beat me to it with his answer which is just bout the same. I do build the string differently, and have a complete working example.

try this:

CREATE TABLE YourTable  (SoNo int, SoItem int, SoRels char(3),  LotSerial char(4))
go
INSERT INTO YourTable VALUES (123456,1,'001','ABCD')
INSERT INTO YourTable VALUES (123456,1,'001','AMOH')
INSERT INTO YourTable VALUES (123456,1,'001','POWK')
INSERT INTO YourTable VALUES (123456,1,'001','IUIL')
INSERT INTO YourTable VALUES (123456,1,'002','ABCE')
go
CREATE FUNCTION LotSerial_to_CVS(@SoNo int, @SoItem int, @SoRels char(3))
RETURNS varchar(2000) AS
BEGIN
    DECLARE @cvs varchar(2000)
    SELECT @cvs=ISNULL(@cvs+', ','')+LotSerial
        FROM YourTable
        WHERE SoNo=@SoNo AND SoItem=@SoItem AND SoRels=@SoRels
    RETURN @cvs
END
go

SELECT
    SoNo, SoItem, SoRels, dbo.LotSerial_to_CVS(SoNo, SoItem, SoRels)
    FROM YourTable
        GROUP BY SoNo, SoItem, SoRels

OUTPUT:

SoNo        SoItem      SoRels 
----------- ----------- ------ -----------------------
123456      1           001    ABCD, AMOH, POWK, IUIL
123456      1           002    ABCE

(2 row(s) affected)

Answer 3

There is no need for a cursor in almost ANY case any more.

You can do the same thing using XML PATH as well. Here is a working sample:

SET NOCOUNT ON

Declare @MyTable Table
(
    SoNo VarChar (100),
    SoItem VarChar (100),
    SoRels VarChar (100),
    LotSerial VarChar (100)
)

INSERT INTO @MyTable Values ('123456', '1', '001', 'ABCD')
INSERT INTO @MyTable Values ('123456', '1', '001', 'AMOH')
INSERT INTO @MyTable Values ('123456', '1', '001', 'POWK')
INSERT INTO @MyTable Values ('123456', '1', '001', 'IUIL')
INSERT INTO @MyTable Values ('123456', '1', '002', 'ABCE')

SELECT 
    SoNo, 
    SoItem, 
    SoRels, 
    STUFF ((
     SELECT ', ' + LotSerial 
     FROM @MyTable T1
     WHERE 1=1
      AND T1.SoNo = T2.SoNo
      AND T1.SoItem = T2.SoItem
      And T1.SoRels = T2.SoRels
     FOR XML PATH ('')
    ), 1, 2, '') AS LotSerial
FROM @MyTable T2
GROUP BY SoNo, SoItem, SoRels