quote:

---

Originally posted by pk_bohra

Try this:

DECLARE @Sub_Book_Affliation TABLE (BookNumber INT, Sub_Aff_ID int)

INSERT @Sub_Book_Affliation
SELECT 3678, 4099 UNION ALL
SELECT 3678, 4111 UNION ALL
SELECT 3678, 4112 UNION ALL
SELECT 3678, 4113 UNION ALL
SELECT 3550, 1001 UNION ALL
SELECT 3550, 1002


-- Show the expected output
SELECT DISTINCT s1.BookNumber,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + Cast(s2.Sub_Aff_ID as varchar)
FROM @Sub_Book_Affliation AS s2 WHERE s2.BookNumber = s1.BookNumber
ORDER BY ',' + Cast(s2.Sub_Aff_ID as varchar) FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sub_Book_Affliation AS s1
ORDER BY s1.BookNumber


Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

---

DECLARE @T TABLE
(BookNumber int,
Sub_Aff_ID int,
UNIQUE (BookNumber, Sub_Aff_ID));

INSERT INTO @T (BookNumber, Sub_Aff_ID)
SELECT 3678, 4099 UNION
SELECT 3678, 4111 UNION
SELECT 3678, 4112 UNION
SELECT 3678, 4113 UNION
SELECT 3550, 1001 UNION
SELECT 3550, 1002

SELECT BookNumber,
       MAX(CASE WHEN rec_id = 1 THEN CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) + 
       MAX(CASE WHEN rec_id = 2 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) +
       MAX(CASE WHEN rec_id = 3 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) +
       MAX(CASE WHEN rec_id = 4 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) +
       MAX(CASE WHEN rec_id = 5 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) +
       MAX(CASE WHEN rec_id = 6 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) +
       MAX(CASE WHEN rec_id = 7 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) +
       MAX(CASE WHEN rec_id = 8 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) +
       MAX(CASE WHEN rec_id = 9 THEN ', ' + CAST(Sub_Aff_ID AS VARCHAR(10)) ELSE '' END) AS list
FROM
(SELECT BookNumber, 
       Sub_Aff_ID, 
       (SELECT COUNT(*)
          FROM @T 
         WHERE BookNumber = t.BookNumber
           AND Sub_Aff_ID <= t.Sub_Aff_ID) AS rec_id
FROM @T AS t)D
GROUP BY BookNumber;

/*
BookNumber  list
----------- ------------------------
3550        1001, 1002
3678        4099, 4111, 4112, 4113
*/

quote:

---

Originally posted by madhivanan

Also refer Quiry update method
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by ms65g

quote:

---

Originally posted by madhivanan

Also refer Quiry update method
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

---

Concatenating rows with assignment update! nice idea.
Could you please say me why do we call the assignmet update to 'quirky updet' ?

---