关于SQL SERVER 行变列，老生常谈
老生常谈了，但是，我还是遇到新问题
ID NAME
---------------
001 NAME1
002 NAME2
003 NAME1
004 NAME2
005 NAME2
006 NAME1
我希望得到结果
NAME ID
-----------
NAME1 1,3,6
NAME2 2,4,5
用group by cube/rollup等，会出现为NULL的栏位，比如
NAME ID1 ID2 ID3 ID4 ID5 ID6
------------------------------------------------------
NAME1 1 NULL 3 NULL NULL 6
NAME2 NULL 2 NULL 4 5 NULL
求助该怎么做 group?by?cube/rollup 行变列
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-05 10:18:38
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] varchar(3),[NAME] varchar(5))
insert [huang]
select '001','NAME1' union all
select '002','NAME2' union all
select '003','NAME1' union all
select '004','NAME2' union all
select '005','NAME2' union all
select '006','NAME1'
--------------开始查询--------------------------
select a.NAME,
stuff((select ','+CONVERT(VARCHAR(100),CONVERT(INT,[ID])) from [huang] b
where b.NAME=a.NAME
for xml path('')),1,1,'') 'ID'
from [huang] a
group by a.NAME
----------------结果----------------------------
/*
NAME ID
----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NAME1 1,3,6
NAME2 2,4,5
*/
