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中做处理了。。。。参数在传之前处理。