SQL 行转列（SQL请教）

[yuelailiu]

sku,pic_name（图片名称）,show_order（排序）
101, A.jpg, 0
101,B.jpg,1
101,C.jpg,2
102,,D.jpg,1
102,E.jpg,2
...
一个SKU，可能存在多个图片名称，注意最后一个字段是排序字段

希望的结果,按SKU显示一行,show_order排序，每个图片中间 | 分割

101,A.jpg|B.jpg|C.jpg
102 D.jpg|E.jgp

请高手指导

[wangnaisheng]

引用 1 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-09 07:49:17
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([sku] nvarchar(24),pic_name nvarchar(12),show_order int)
insert [huang]
select '101','A.jpg',0 union all
select '101','B.jpg',1 union all
select '101','C.jpg',2 union all
select '102','D.jpg',1 union all
select '102','E.jpg',2
--------------生成数据--------------------------


select a.[sku],
stuff((select '|'+pic_name from [huang] b 
       where b.[sku]=a.[sku] 
       ORDER BY b.show_order
       for xml path('')) ,1,1,'') 'pic_name'		
from [huang] a
group by  a.[sku]
----------------结果----------------------------
/* 
sku                      pic_name
------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101                      A.jpg|B.jpg|C.jpg
102                      D.jpg|E.jpg
*/

备用一下。

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-09 07:49:17
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([sku] nvarchar(24),pic_name nvarchar(12),show_order int)
insert [huang]
select '101','A.jpg',0 union all
select '101','B.jpg',1 union all
select '101','C.jpg',2 union all
select '102','D.jpg',1 union all
select '102','E.jpg',2
--------------生成数据--------------------------


select a.[sku],
stuff((select '|'+pic_name from [huang] b 
       where b.[sku]=a.[sku] 
       ORDER BY b.show_order
       for xml path('')) ,1,1,'') 'pic_name'		
from [huang] a
group by  a.[sku]
----------------结果----------------------------
/* 
sku                      pic_name
------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101                      A.jpg|B.jpg|C.jpg
102                      D.jpg|E.jpg
*/