我覺得當你需要行列轉換的時候，有可能是
數據模型
不合理了。
需要考慮考慮是否應該把數據模型修改一下，
將一行多欄位的表形式直接變成多行的表形式。
⑵ 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 合計=廢塑料+廢五金+廢鋼鐵+廢紙+廢有色+廢纖維+其它;
（所有涉及表的行列轉換均可按照這種方式實現。）