下面是多行转列的问题,说明下：这是一个六合彩生肖号码表,买过马的朋友一看应该就知道,YEARS是年度,SX是生肖,如：鼠,牛 HAOMA是生肖所对应的号码,COLOR是生肖的颜色,下面是正常查询出来的数据
ID YEARS SX HAOMA COLOR
2 2013 mouse 06 green
3 2013 mouse 18 red
4 2013 mouse 30 red
5 2013 mouse 42 blue
6 2013 cattle 05 green
7 2013 cattle 17 green
8 2013 cattle 29 red
9 2013 cattle 41 blue
我现在要显示这样格式的数据:
YEARS mouse cattle
2013 06-green 05-green
2013 18-red 17-green
2013 30-red 29-red
2013 42-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