SQL多行转列
下面是多行转列的问题,说明下：这是一个六合彩生肖号码表,买过马的朋友一看应该就知道,YEARS是年度,SX是生肖,如：鼠,牛 HAOMA是生肖所对应的号码,COLOR是生肖的颜色,下面是正常查询出来的数据
IDYEARSSXHAOMACOLOR
22013mouse06green
32013mouse18red
42013mouse30red
52013mouse42blue
62013cattle05green
72013cattle17green
82013cattle29red
92013cattle41blue
我现在要显示这样格式的数据:
YEARSmouse cattle
201306-green 05-green
201318-red 17-green
201330-red 29-red
201342-blue 41-blue
搞了一天没搞出来，望各位朋友指教，这个好像不是一般的行转列问题... SQL 行转列
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-15 17:10:32
-- 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]([ID] int,[YEARS] int,[SX] varchar(6),[HAOMA] varchar(2),[COLOR] varchar(5))
insert [huang]
select 2,2013,'mouse','06','green' union all
select 3,2013,'mouse','18','red' union all
select 4,2013,'mouse','30','red' union all
select 5,2013,'mouse','42','blue' union all
select 6,2013,'cattle','05','green' union all
select 7,2013,'cattle','17','green' union all
select 8,2013,'cattle','29','red' union all
select 9,2013,'cattle','41','blue'
--------------开始查询--------------------------
--select * from [huang]
SELECT *,ROW_NUMBER()OVER(PARTITION BY sx ORDER BY id)pid INTO #t
FROM huang
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(SX)+'=max(case when [SX]='+quotename(SX,'''')+' then HAOMA'+'+''-''+'+'COLOR else ''0'' end)'
from #t group by SX
EXEC ('select [YEARS]'+@s+' from [#t] group by [YEARS],pid')
----------------结果----------------------------
/*
YEARS cattle mouse
----------- -------- --------
2013 05-green 06-green
2013 17-green 18-red
2013 29-red 30-red
2013 41-blue 42-blue
*/
WITH a1 (iD,YEARS,SX,HAOMA,COLOR) AS
(
SELECT 2, 2013,'mouse','06','green' UNION all
SELECT 3, 2013,'mouse','18','red' UNION all
SELECT 4, 2013,'mouse','30','red' UNION all
SELECT 5, 2013,'mouse','42','blue' UNION all
SELECT 6, 2013,'cattle','05','green' UNION all
SELECT 7, 2013,'cattle','17','green' UNION all
SELECT 8, 2013,'cattle','29','red' UNION all
SELECT 9, 2013,'cattle','41','blue'
)
,a2 AS
(
SELECT YEARS,HAOMA+'-'+COLOR mouse,ROW_NUMBER() OVER(ORDER BY GETDATE()) re
FROM a1 WHERE SX='mouse'
)
,a3 AS
(
SELECT YEARS,HAOMA+'-'+COLOR cattle,ROW_NUMBER() OVER(ORDER BY GETDATE()) re
FROM a1 WHERE SX='cattle'
)
SELECT a.YEARS,a.mouse,b.cattle
FROM a2 a
INNER JOIN a3 b ON a.re=b.re
