⑴ sql 2008某字段的列转行转换成列
用povit函数最方便，网络一下
⑵ SQL SERVER 2008 复杂行转列
创建表，数据
createtabletest
(合同编号varchar(10),
付款日期varchar(10),
付款金额int,
序号varchar(2))
insertintotestvalues('JNHS1501','2015-01-01',2600000,1)
insertintotestvalues('JNHS1501','2015-04-01',1000000,2)
insertintotestvalues('JNHS1501','2015-04-27',2000000,3)
insertintotestvalues('JNHS1501','2015-04-16',1500000,4)
insertintotestvalues('JNHS1501','2015-05-26',2000000,5)
insertintotestvalues('JNHS1501','2015-06-16',2000000,6)
insertintotestvalues('JNHS1501','2015-07-29',684770,7)
insertintotestvalues('JNHS1502','2015-05-05',180000,1)
insertintotestvalues('JNHS1502','2015-01-05',50000,2)
执行
declare@sqlvarchar(4000)
set@sql='select合同编号'
select@sql=@sql+',max(case序号when'''+[序号]+'''then[付款日期]end)as
付款日期'+序号+',max(case序号when'''+[序号]+'''then[付款金额]end)as
付款金额'+序号+''
from(selectdistinct序号fromtest)asa
select@sql=@sql+'fromtestgroupby合同编号'
exec(@sql)
结果：
⑶ sqlserver如何列转行
CREATETABLE#test(
snINT,
enINT
);
INSERTINTO#testVALUES(1,5);
INSERTINTO#testVALUES(6,7);
INSERTINTO#testVALUES(8,8);
INSERTINTO#testVALUES(9,12);
go
SELECTDISTINCT
m.number
FROM
#testt
JOINmaster..spt_valuesm
ON(
t.sn<=m.number
ANDt.en>=m.number
);
go
查询结果:
number
-----------
1
2
3
4
5
6
7
8
9
10
11
12
(12行受影响)
⑷ sql2008列转行语句
http://blog.csdn.net/greystar/article/details/5660645
看看上面三种方法是不是你需要的。
⑸ sql server 列转行
SQL Server 2005之后支持pivot关键字。
select year,[1],[2],[3],[4]
from 表A
pivot(max(num) for [month] in ([1],[2],[3],[4])) as pvt
SQL Server 2000以及之前版本，可以参考imlees的方法。
⑹ SqlServer2008中，怎么进行 行转列
--除去第二行，要么你直接delete，要么给一个规则，去最大？最小？还是相加，自己处理一下
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (id int,AttachName int,AttachNum int,AttachReSerNum int,AttachSerNum int)
insert into tb
select 4,1,1,12313,123 union all
select 4,1,1,312313,123131 union all
select 5,2,1,1231231,123123
DECLARE @SQL VARCHAR(8000),@SQL1 VARCHAR(8000)
DECLARE @I INT,@COUNT INT,@COLUMN VARCHAR(20)
SELECT @SQL = ISNULL(@SQL + '],[' , '') + CONVERT(VARCHAR(10),ID) FROM tb GROUP BY ID
SET @SQL = '[' + @SQL + ']'
select @sql1=isnull(@sql1,'')+'SELECT '''+a.name+''' as 属性,'+@sql+'
FROM (
SELECT ID,MIN('+a.name+') as '+a.name+'
FROM tb
group by ID
) A PIVOT (MAX('+a.name+') FOR ID IN (' + @SQL + ')) B union all
'
from sys.all_columns as a
where a.object_id=object_id('tb') and a.name!='ID'
set @SQL1=substring(@sql1,0,len(@sql1)-11)
exec(@sql1)
------------------------------------
属性 4 5
-------------- ----------- -----------
AttachName 1 2
AttachNum 1 1
AttachReSerNum 12313 1231231
AttachSerNum 123 123123
⑺ SQL Server 带条件的列转行
这样写就可以了。已测试，没有问题。
SELECT a.dep_id,
max(CASE WHEN a.route_id = 1 THEN a.apt ELSE NULL end) astart,
max(CASE WHEN a.route_id = 2 AND a.route_id < b.max_route THEN a.apt ELSE NULL end) via1,
max(CASE WHEN a.route_id = 3 AND a.route_id < b.max_route THEN a.apt ELSE NULL end) via2,
max(CASE WHEN a.route_id = b.max_route THEN a.apt ELSE NULL end) dest
FROM flight_info a,
(SELECT dep_id, MAX(route_id) max_route FROM flight_info GROUP BY dep_id) b
WHERE a.dep_id = b.dep_id
GROUP BY a.dep_id
ORDER BY a.dep_id
注：其中 flight_info 是表名。记得采纳哈。
测试结果：
dep_id astart via1 via2 dest
111112 SHE PEK CSX
111114 SHE CGD CGO
111115 SHE CGQ CIF
111118 SHE CTU
111119 SHE KWE
111120 SHE HGH
111121 SHE KMG
111122 SHE LHW
111123 SHE LXA
111124 SHE TAO
111125 SHE TSN
⑻ SQL Server 2008列转行问题
第一个结果的格式， 你先看看？合适不合适？
With myCTE AS (
select
ROW_NUMBER() over(partition by begin_time, end_time, LEFT(op_id,1) ORDER BY op_id) AS NO,
operator.*
from
operator
)
SELECT
begin_time AS [开始时间],
end_time AS [结束时间],
MAX(CASE WHEN LEFT(op_id,1) = '1' THEN op_id else '' END) AS [一组工号],
MAX(CASE WHEN LEFT(op_id,1) = '1' THEN work_time else NULL END) AS [工作时长],
MAX(CASE WHEN LEFT(op_id,1) = '2' THEN op_id else '' END) AS [二组工号],
MAX(CASE WHEN LEFT(op_id,1) = '2' THEN work_time else NULL END) AS [工作时长],
MAX(CASE WHEN LEFT(op_id,1) = '3' THEN op_id else '' END) AS [三组工号],
MAX(CASE WHEN LEFT(op_id,1) = '3' THEN work_time else NULL END) AS [工作时长]
FROM
myCTE
GROUP BY
begin_time, end_time, NO
第二个结果的格式
SELECT
begin_time AS [开始时间],
end_time AS [结束时间],
SUM( CASE WHEN LEFT(op_id,1) = '1' THEN 1 ELSE 0 END ) AS [一组上班人数],
SUM( CASE WHEN LEFT(op_id,1) = '2' THEN 1 ELSE 0 END ) AS [二组上班人数],
SUM( CASE WHEN LEFT(op_id,1) = '3' THEN 1 ELSE 0 END ) AS [三组上班人数]
FROM
operator
GROUP BY
begin_time, end_time
⑼ 关于SQL SERVER 2008 列转行的问题
给你一个网址，说的比较详细 http://blog.csdn.net/yubofighting/article/details/6767390