Column values in a row

Question

Column values in a row

Roy Rogers

SSCertifiable

Points: 7712
More actions
May 31, 2005 at 8:55 am

#69095

How do we provide related column values in a row. As per the following code a cursor is used to achive this. This takes a lot of time. Is there a way to get similar results in a direct query witout cursor. Thanks in advance.
SET NOCOUNT ON
GO
CREATE TABLE #Machine
(
MachineNo INT,
MachinePart     VARCHAR(32)
)
INSERT #Machine VALUES (1, 'MACHINE 1-1')
INSERT #Machine VALUES (1, 'MACHINE 1-2')
INSERT #Machine VALUES (1, 'MACHINE 1-3')
INSERT #Machine VALUES (1, 'MACHINE 1-4')
INSERT #Machine VALUES (2, 'MACHINE 2-1')
INSERT #Machine VALUES (3, 'MACHINE 3-1')
INSERT #Machine VALUES (3, 'MACHINE 3-2')
INSERT #Machine VALUES (3, 'MACHINE 3-3')
INSERT #Machine VALUES (4, 'MACHINE 4-1')
INSERT #Machine VALUES (4, 'MACHINE 4-2')
INSERT #Machine VALUES (5, 'MACHINE 5-1')
GO
SELECT * FROM #Machine
GO
   DECLARE @MachineNo   INT
   DECLARE @MachinePart VARCHAR(1000)
   DECLARE @Machine     TABLE (MachineNo INT, MachinePart VARCHAR(1000))
   DECLARE Mac_cursor CURSOR FOR
   SELECT DISTINCT MachineNo
   FROM #Machine
   OPEN Mac_cursor
   FETCH NEXT FROM Mac_cursor INTO @MachineNo
   WHILE @@FETCH_STATUS = 0
   BEGIN

      SET @MachinePart = ''
      SELECT @MachinePart = CASE @MachinePart WHEN '' THEN '' ELSE @MachinePart + '<BR>' END + MachinePart
      FROM
    #Machine
      WHERE
    MachineNo = @MachineNo
      INSERT @Machine VALUES (@MachineNo, @MachinePart)
      FETCH NEXT FROM Mac_cursor INTO @MachineNo

   END
   CLOSE Mac_cursor
   DEALLOCATE Mac_cursor
SELECT * FROM @Machine
Regards,
gova

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Are there a limited number of matching values? Are there a limited number of columns you are looking for? Self joins will handle this, but you'd have to know how many values. Otherwise just go with the cursor. It's slow, but simpler.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

Thank You Steve.

There are about 2000 Machines. Parts each machine has now is 1 to 12.

There are other columns, they are properties of machine so they are not relevent here. One would't say it is good design. But schema cannot be changed. Result needed is as per result in the example.

But I would like to develop a query without using a loop or cursor if that enhances the performance. I can't think of a query with self join. Please post a query for the table above.

Thanks.

Regards,
gova

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

Here is one way,

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.fn_MachineParts') and xtype in (N'FN', N'IF', N'TF'))

drop function dbo.fn_MachineParts

GO

CREATE FUNCTION dbo.fn_MachineParts

(

@MachineNo int

)

RETURNS varchar(1000)

AS

BEGIN

DECLARE @MachineParts varchar(1000)

SELECT @MachineParts = ISNULL(@MachineParts+', ','')+MachinePart

FROM Machine

WHERE MachineNo = @MachineNo

RETURN @MachineParts

END

GO

SELECT MachineNo, dbo.fn_MachineParts(MachineNo) AS MachineParts

FROM Machine

GROUP BY MachineNo

Andy

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Another one:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

Thanks David That does the trick. Can we say still it is in a hidden loop.

I did in a different way. My function returns a table with all the machine which has multiple columns with concatination. Then I make a LEFT JOIN with main table to get the result.

Your function with single val is far better as I filter the rows in most occasions.

--

Thanks Frank

Regards,
gova