⑴ 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