求个 行转列
表中 有数据
mid mvalue
1 1
2 1
3 1
1 2
2 2
3 2
1 3
2 3
3 3
1 4
2 4
3 4
........
求效果为
mid n1,n2,n3
1 1 2 3
2 1 2 3
3 1 2 3
4 1 2 3
........
[解决办法]
----------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-09-24 15:30:18
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([mid] int,[mvalue] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 1,2 union all
select 2,2 union all
select 3,2 union all
select 1,3 union all
select 2,3 union all
select 3,3 union all
select 1,4 union all
select 2,4 union all
select 3,4
--------------开始查询--------------------------
SELECT
mvalue,
MAX(CASE WHEN px=1 THEN mid END) AS n1,
MAX(CASE WHEN px=2 THEN mid END) AS n2,
MAX(CASE WHEN px=3 THEN mid END) AS n3
FROM
(SELECT *, px=(SELECT COUNT(1)+1 FROM TB WHERE mvalue=t.mvalue AND mid<t.mid) FROM TB T)t
GROUP BY
mvalue
----------------结果----------------------------
/* mvalue n1 n2 n3
----------- ----------- ----------- -----------
1 1 2 3
2 1 2 3
3 1 2 3
4 1 2 3
警告: 聚合或其他 SET 操作消除了 Null 值。
(4 行受影响)
*/
WITH a1 (mid,mvalue) AS
(
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,4
)
SELECT mvalue AS mid,[1] AS n1,[2] AS n2,[3] AS n3
FROM a1
PIVOT (MAX(mid) FOR mid IN ([1],[2],[3])) b
2005+
SELECT
mvalue,
MAX(CASE WHEN px=1 THEN mid END) AS n1,
MAX(CASE WHEN px=2 THEN mid END) AS n2,
MAX(CASE WHEN px=3 THEN mid END) AS n3
FROM
(SELECT *, px=ROW_NUMBER() OVER(PARTITION BY mvalue ORDER BY mid) FROM TB )t
GROUP BY
mvalue
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-24 15:13:26
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation 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]([mid] int,[mvalue] int)
insert [huang]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 1,2 union all
select 2,2 union all
select 3,2 union all
select 1,3 union all
select 2,3 union all
select 3,3 union all
select 1,4 union all
select 2,4 union all
select 3,4
--------------开始查询--------------------------
--select [mvalue],[mid] from [huang]
SELECT mvalue as mid,PARSENAME(REPLACE(mid,',','.'),3) 'n1',PARSENAME(REPLACE(mid,',','.'),2) AS 'n2',PARSENAME(REPLACE(mid,',','.'),1) AS 'n3'
FROM (
select CONVERT(VARCHAR(10),a.mvalue)mvalue,stuff((select ','+CONVERT(VARCHAR(64),mid) from huang b
where CONVERT(VARCHAR(10),b.mvalue)=CONVERT(VARCHAR(10),a.mvalue)
for xml path('')),1,1,'') 'mid'
from huang a
group by CONVERT(VARCHAR(10),a.mvalue))a
----------------结果----------------------------
/*
mid n1 n2 n3
---------- -------------------------------------------------------- -------------------------------------------------------- --------------------------------------------------------
1 1 2 3
2 1 2 3
3 1 2 3
4 1 2 3
*/
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-24 15:13:26
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation 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]([mid] int,[mvalue] int)
insert [huang]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 1,2 union all
select 2,2 union all
select 3,2 union all
select 1,3 union all
select 2,3 union all
select 3,3 union all
select 1,4 union all
select 2,4 union all
select 3,4
--------------开始查询--------------------------
--select [mvalue],[mid] from [huang]
SELECT mvalue as mid,mid
FROM (
select CONVERT(VARCHAR(10),a.mvalue)mvalue,stuff((select ','+CONVERT(VARCHAR(64),mid) from huang b
where CONVERT(VARCHAR(10),b.mvalue)=CONVERT(VARCHAR(10),a.mvalue)
for xml path('')),1,1,'') 'mid'
from huang a
group by CONVERT(VARCHAR(10),a.mvalue))a
----------------结果----------------------------
/*
mid mid
---------- -------------
1 1,2,3
2 1,2,3
3 1,2,3
4 1,2,3
*/
CREATE TABLE #Test(ID INT,Num INT)
INSERT INTO #Test
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,4
DECLARE @sql VARCHAR(500)
SELECT @sql = ISNULL(@sql + ',' , '') + '['+CAST(Num AS VARCHAR)+']' FROM #Test GROUP BY Num
EXEC ('select ID,'+@sql+'
from (SELECT ID
,CAST(Num AS VARCHAR) AS Num
FROM #Test) AS T
pivot (max(T.Num) for T.Num in (' + @sql + ')) b')
