表A：
ID BOOK SHOE BAG TELE PEN
1 2 4 3 1 0
要求得到如下结果：
ID NOTE
1 BOOK * 2,SHOE * 4,BAG * 3,TELE * 1,PEN * 0 (PEN * 0 能不显示最好，即显示：BOOK * 2,SHOE * 4,BAG * 3,TELE * 1）
------解决方案--------------------
- SQL code
select id,'book*'+ltrim(book)+','+'shoe*'+ltrim(shoe)+','+'bag*'+ltrim(bag)+','+'tele*'+ltrim(tele)+','+'pen*'+ltrim(pen) as notefrom ta
------解决方案--------------------
- SQL code
select id,'book*'+ltrim(book)+','+'shoe*'+ltrim(shoe)+','+'bag*'+ltrim(bag)+','+'tele*'+ltrim(tele) as notefrom tb
------解决方案--------------------
- SQL code
--------------------------------------- Author : liangCK 梁爱兰-- Comment: 小梁 爱 兰儿-- Date : 2009-11-16 13:21:51------------------------------------- --> 生成测试数据: @tbDECLARE @tb TABLE (ID int,BOOK int,SHOE int,BAG int,TELE int,PEN int)INSERT INTO @tbSELECT 1,2,4,3,1,0 UNION ALLSELECT 2,10,5,3,1,5--SQL查询如下:DECLARE @x xml;SET @x = (SELECT * FROM @tb FOR XML RAW('element'),TYPE);SELECT T.x.value([email protected]','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*12 BOOK*10 ,SHOE*5 ,BAG*3 ,TELE*1 ,PEN*5(2 行受影响)*/