select id,'book*'+ltrim(book)+','+'shoe*'+ltrim(shoe)+','+'bag*'+ltrim(bag)+','+'tele*'+ltrim(tele)+','+'pen*'+ltrim(pen) as note
from ta

------解决方案--------------------
SQL code
select
 id,'book*'+ltrim(book)+','+'shoe*'+ltrim(shoe)+','+'bag*'+ltrim(bag)+','+'tele*'+ltrim(tele) as note
from
  tb

------解决方案--------------------
SQL code
-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-11-16 13:21:51
-------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID int,BOOK int,SHOE int,BAG int,TELE int,PEN int)
INSERT INTO @tb
SELECT 1,2,4,3,1,0 UNION ALL
SELECT 2,10,5,3,1,5

--SQL查询如下:

DECLARE @x xml;
SET @x = (SELECT * FROM @tb FOR XML RAW('element'),TYPE);

SELECT T.x.value('@ID','int') AS ID,
    NOTE = STUFF(CAST(T.x.query('for $i in ./@*[local-name()!="ID"]
                          where number($i) != 0
                          return concat(",",local-name($i),"*",string($i))') AS varchar(MAX))
                 ,1,1,'')
FROM @x.nodes('/element') AS T(x)

/*
ID          NOTE
----------- --------------------------
1           BOOK*2 ,SHOE*4 ,BAG*3 ,TELE*1
2           BOOK*10 ,SHOE*5 ,BAG*3 ,TELE*1 ,PEN*5

(2 行受影响)
*/