sqlserver2008行轉列

① sql server 行轉列

創建測試表

createtabletest
(_keyvarchar(10),
_valuevarchar(10),
idint)

insertintotestvalues('ceshi','測試值',10)
insertintotestvalues('ceshi','測試值',11)
insertintotestvalues('ceshi2','測試值2',11)

執行

declare@sqlvarchar(4000)
set@sql='selectid'
select@sql=@sql+',max(case_keywhen'''+[_key]+'''then[_value]end)as
['+_key+']'
from(selectdistinct_keyfromtest)asa
select@sql=@sql+'fromtestgroupbyid'
exec(@sql)

結果

額，我那個第三條數據id寫錯了，不過方法還是這個

② sql server 2008 如何行轉列

這里只有一列嗎？
如果資料庫只有這url這一列，可以使用如下方法：
select url1 as url from table2
union all
select url2 as url from table2
union all
select url3 as url from table2
union all
select ur42 as url from table2
樓主，如果可用還望採納！

③ 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)

結果：

④ 關於SQL SERVER 2008 列轉行的問題

給你一個網址，說的比較詳細 http://blog.csdn.net/yubofighting/article/details/6767390

⑤ 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

⑥ sqlserver2008 特殊動態行轉列 急！！！

你先把你上邊的查詢語句建立個視圖吧

這個會吧？

我就直接拿你的數據建表了

createtablet(bf_org_shop_IDvarchar(10),
prod_IDvarchar(10),
sales_priceint,
sales_qtyint)insertintotvalues('單位1','aa',12,13)
insertintotvalues('單位1','bb',14,15)
insertintotvalues('單位2','aa',12,17)
insertintotvalues('單位2','bb',14,19)
insertintotvalues('單位3','aa',12,21)
insertintotvalues('單位3','bb',14,23)

執行

declare@sqlvarchar(4000)
set@sql='select[prod_ID],[sales_price]'
select@sql=@sql+',sum(isnull(case[bf_org_shop_ID]when'''+[bf_org_shop_ID]+'''then[sales_qty]end,0))as
['+[bf_org_shop_ID]+']'
from(selectdistinct[bf_org_shop_ID]from[t])asa
select@sql=@sql+'from[t]groupby[prod_ID],[sales_price]'
exec(@sql)

截圖

⑦ 請教sqlserver2008 行轉列的SQL語法

SELECT
SUM ( CASE WHEN 時間 = '2012-11' THEN 1 ELSE 0 END ) AS [2012-11],
SUM ( CASE WHEN 時間 = '2012-12' THEN 1 ELSE 0 END ) AS [2012-12]
FROM
表

如果要動態的產生 [2012-01] .....[2013-01] 這種情況的話， 需要寫存儲過程， 用動態 SQL 來處理了。

⑧ 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中一個行轉列的SQL

select 姓名 ,sum(case 科目 when '語文' then 分數 else 0 end) as 語文
,sum(case 科目 when '數學' then 分數 else 0 end) as 數學

from 表 group by 姓名

其他的照樣加進去

⑩ 怎樣用sqlserver將查詢結果行轉列

<select id="getList" parameterType="ActRebate" resultMap="actRebateResultMap">
select t.* from t_rule_rebate t where (merchantId IS null OR t.merchantid = #{merchantId} )
AND (ztbz IS null OR trim(t.ztbz) = #{ztbz})
AND (type IS null OR trim(t.type) = #{type})
</select>
傳進去的參數就不要在SQL中做處理了。。。。參數在傳之前處理。