我觉得当你需要行列转换的时候，有可能是
数据模型
不合理了。
需要考虑考虑是否应该把数据模型修改一下，
将一行多字段的表形式直接变成多行的表形式。
⑵ 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如何将同一张表两行变成另一张表或视图的两列
CREATE TABLE #A (
A char(2),
B char(2),
C char(2)
);
INSERT INTO #A
SELECT 'A1', 'B1', 'C1' UNION ALL
SELECT 'A2', 'B2', 'C2';
GO
WITH myCTE AS(
SELECT
ROW_NUMBER() OVER( ORDER BY (SELECT 1)) AS NO,
A,B,C
FROM
#A
)
SELECT
MAX( CASE WHEN NO = 1 THEN A ELSE NULL END ) D,
MAX( CASE WHEN NO = 2 THEN A ELSE NULL END ) E
FROM
myCTE
UNION ALL
SELECT
MAX( CASE WHEN NO = 1 THEN B ELSE NULL END ) D,
MAX( CASE WHEN NO = 2 THEN B ELSE NULL END ) E
FROM
myCTE
UNION ALL
SELECT
MAX( CASE WHEN NO = 1 THEN C ELSE NULL END ) D,
MAX( CASE WHEN NO = 2 THEN C ELSE NULL END ) E
FROM
myCTE
GO
D E
-- --
A1 A2
B1 B2
C1 C2
==========
SELECT
B.NAME,
A.VALUE
FROM
A JOIN B
ON ( A.ID = B.ID )
UNION ALL
SELECT
D.NAME,
C.VALUE
FROM
C JOIN D
ON ( C.ID = C.ID )
⑸ SQL Server 2008 R2 存储过程行列转换问题
给你略改了一下，把双引号，换成2个单引号
alterPROCEDURE[dbo].[统计]
@ksrqvarchar(10),
@jsrqvarchar(10)
AS
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+'max(case科目when'''+科目+'''then成绩else0end)['+科目+']'
from(selectdistinct科目from表)asa
set@sql='select姓名,'+@sql+'from表where([日期]between'''+@ksrq+'''and'''+@jsrq+''')groupby姓名'
exec(@sql)
执行
declare@ksrqvarchar(10)
declare@jsrqvarchar(10)
set@ksrq='2013-07-25'
set@jsrq='2013-08-05'
exec[dbo].[统计]@ksrq,@jsrq
结果，2000下
⑹ 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中，怎么进行 行转列
--除去第二行，要么你直接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
⑻ SQL2008 存储过程行列转换
ALTERPROC[dbo].[Report_Sale_JXS_HFgongdan]
(@BeginDateDatetime
,@EndDatedatetime
,@Name1VARCHAR(50)
,@Name2VARCHAR(50))
AS
BEGIN
if@EndDate<='2010-01-01'
begin
select@EndDate=GETDATE()
end
SELECT
T0.CARDNAMEAS'客户名称',T0.CARDPHONEAS'客户电话',T0.VINAS'车架号',T0.MNameAS'车型描述'
,T4.OColorNameAS'车身颜色',T0.NoteAS'备注',T0.JYNoteAS'建议',T6.U001AS'事业部',T4.CmpCodeAS'经销商编号'
,T0.SaleNameAS'经销商名称',T0.SalePhoneAS'经销商电话',CONVERT(VARCHAR(10),T0.DocDay,23)AS'来电日期'
--,T3.L1DESCAS'问题'
--,T3.L1AnswerAS'回答'
--,T3.L1ScoreAS'得分'
,MAX(CASEWHENT3.L1DESC='XXX问题'THENT3.L1AnswerEND)AS'XXX问题的回答'
--所有问题按上面那样子写出来
,MAX(CASEWHENT3.L1DESC='XXX问题'THENT3.L1ScoreEND)AS'XXX问题的得分'
--所有问题按上面那样子写出来
,T0.[Status]AS'回访状态',T0.DocTotalAS'综合得分',T0.HFTypeNameAS'回访类型',T2.userNameAS'回访人员'
,casewhenT0.IsHFCG='1'then'是'
whenT0.IsHFCG='0'then'否'endAS'是否成功'
,casewhenT0.IsClose='1'then'是'
whenISNULL(T0.IsClose,0)='0'then'否'endas'是否结束'
FROMdmsSaleHFDJT0
innerJOINdmsSaleHFLXT1ONT0.HFTypeName=T1.[DESC]
innerjoindmsBaseUserT2onT0.HFPsnName=T2.userName
innerjOINdmssaleHFDJ1T3ONT3.ID=T0.ID
INNERjoindmsSaleOCarT4onT4.VIN=T0.VIN
innerjoindmsBaseUserT5onT5.userCode=T4.CmpCode
innerjoindmsBaseChinaT6ONT5.province=T6.Id
whereconvert(varchar(10),T0.DocDay,23)>=@BeginDate
andconvert(varchar(10),T0.DocDay,23)<=@EndDate
and(T0.HFTypeName=@Name1or@Name1='')
and(T0.HFPsnName=@Name2or@Name2='')
----------
GROUPBY'除T3.L1DESC、T3.L1Answer、T3.L1Score所有列'
END
或者直接在报表里面列分组
⑼ sql语句怎么把列变成行
create table rotatetable1 (序号 int,company char(66),box_weight char(12),废塑料numeric(10,2)),废五金 numeric(10,2)),废钢铁 numeric(10,2)),废纸 numeric(10,2)),废有色 numeric(10,2)),废纤维 numeric(10,2)),其它 numeric(10,2)),合计 numeric(10,2)));
insert into rotatetable1(company,box_weight) select name ,'weight' from sum1 group by name;
insert into rotatetable1(company,box_weight) select name ,'box' from sum1 group by name;
update rotatetable1 set 废塑料=box from sum1as a where a.name=rotatetable1.company and box_weight='box' and hsname='废塑料';
update rotatetable1 set 废塑料=weight from sum1as a where a.name=rotatetable1.company and box_weight='weight' and hsname='废塑料';
::: :::
update rotatetable1 set 其它=box from sum1as a where a.name=rotatetable1.company and box_weight='box' and hsname='其它';
update rotatetable1 set 其它=weight from sum1as a where a.name=rotatetable1.company and box_weight='weight' and hsname='其它';
::: :::
update rotatetable1 set 合计=废塑料+废五金+废钢铁+废纸+废有色+废纤维+其它;
（所有涉及表的行列转换均可按照这种方式实现。）