表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)
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)
from ta
id,'book*'+ltrim(book)+','+'shoe*'+ltrim(shoe)+','+'bag*'+ltrim(bag)+','+'tele*'+ltrim(tele) as note
from
tb
id,'book*'+ltrim(book)+','+'shoe*'+ltrim(shoe)+','
+'bag*'+ltrim(bag)+','+'tele*'+ltrim(tele)+','+'pen*'+ltrim(pen) as note
from
tb
-- 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 行受影响)
*/
id,
(case when book=0 then '' else 'book*'+ltrim(book)+',' end)
+(case when shoe=0 then '' else 'shoe*'+ltrim(shoe)+',' end)
+(case when bag=0 then '' else 'bag*'+ltrim(bag)+',' end)
+(case when tele=0 then '' else 'tele*'+ltrim(tele)+',' end)
+(case when pen=0 then '' else 'pen*'+ltrim(pen) end)
as note
from
A