Concatenate values of those column values to single having same ID

Suppose there is table name Items having id and item name. Now you want to show that the items name having same id are show in one row and with all the item name in single column of the row having same id.

Below are the steps:

First Create a table

CREATE TABLE items(id INT, itemname VARCHAR(30))

GO

Inserting values to items table

INSERT INTO items(id,itemname) VALUES (1, 'Pen'),(2,'Pencil'),(3,'Rubber'),(1,'Scale'),(1,'Copy'),

(3,'Box'),(2,'Compas'),(4,'Stapler'),(5,'Paper'),(6,'Ink')

GO

SELECT * FROM items

GO

/* Below is the query to get the desired out put */

DECLARE @ctr INT

DECLARE @val INT

DECLARE @itemName VARCHAR(30)

CREATE TABLE #tempid(row INT IDENTITY(1,1), id INT, itemnames VARCHAR(30))

CREATE TABLE #tempitems (id CHAR(4), itemname VARCHAR(1000))

INSERT INTO #tempid(id, itemnames) SELECT id, itemname FROM items

SET @ctr = (SELECT COUNT(id) FROM items)

WHILE @ctr > 0

BEGIN

      SET @val = (SELECT id FROM #tempid WHERE row = @ctr)

      SET @itemName = (SELECT itemnames FROM #tempid WHERE row = @ctr)

      IF NOT EXISTS (SELECT id FROM #tempitems WHERE CONVERT(INT,id) = @val)

            BEGIN

                  INSERT INTO #tempitems (id, itemname) VALUES (@val, @itemName)

            END

      ELSE

            BEGIN

                  UPDATE #tempitems SET itemname += ', '+ @itemName

                  WHERE id = @val

            END

      SET @ctr = @ctr -1

END

SELECT * FROM #tempitems ORDER BY id

DROP TABLE #tempid, #tempitems

Copy and paste the above script and run...