在sql中将行转换为列 [英] Convert row to column in sql
本文介绍了在sql中将行转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有桌子如
Order_Id InventoryId ItemType total
1 5 Orange 5000
1 4 Apple 3000
2 1 Mango 3400
2 5 Orange 1700
如果我通过订单ID'1'我需要记录如
Orange Apple
5000 3000
如果我通过订单ID'2'我需要记录如
芒果橙
3400 1700
我怎样才能实现这个目标任何人都可以帮助。
解决方案
你听说过 Pivots [ ^ ]?
MSDN KB:
如何使用T-SQL捕获数据? [ ^ ]
SQL Server中的数据透视表。一个简单的样本。 [ ^ ]
CP-QA KB:
如何在T-SQL中执行数据透视操作..? [
使用方式-Pinot-in-SQL-Query>在SQL查询中使用Pivot的简单方法 [ ^ ]
SQL - 旋转全部总列和行 [ ^ ]
声明 @ OrderId int = 2
如果(( select distinct 1 来自 Tem 其中 Order_Id = @ OrderId )= 1)
开始
选择 Orange,Apple,Mango 来自
( select Order_Id,ItemType,total From Tem 其中 Order_Id = @OrderId )u
pivot(sum(total) for ItemType中的class =code-keyword>([Orange],[Apple],[Mango])) pvt
end
将动态查询与Pivot一起使用...尝试这样的事情......
声明 @ Id Int , @ Sql varchar ( 400 ), @ Cols varchar ( 40 )
创建 表 #Temp
(
Order_Id Int ,
InventoryId Int ,
ItemType Varchar ( 40 ),
总计 Int
)
将 插入 #Temp
值( 1 , 5 ,' Orange', 5000 ),( 1 , 4 ,' Apple', 3000 ),( 2 , 1 ,' 芒果', 3400 ),( 2 , 5 ,' 橙色', 1700 )
设置 @ Id = 2 - 如果你是id 1的nt记录设置@ Id = 1
设置 @ Cols = Stuff((选择 不同 ' ], [' + ItemType 来自 #Temp 其中 Order_Id = @ Id
for Xml Path(' '),类型)。value(' 。',< span class =code-string>' VARCHAR(Max)'), 1 , 2 ,' ')+ < span class =code-string>' ]'
Set @ Sql = ' 选择' + @ Cols + ' From(Select ItemType,total From #Temp其中Order_Id ='
+ Cast( @ Id as Varchar )+ ' )st pivot(Sum(total)For ItemType in('
+ @ Cols + ' ))Pvt'
Exec ( @ Sql )
Drop 表 #Temp
I have table such as
Order_Id InventoryId ItemType total 1 5 Orange 5000 1 4 Apple 3000 2 1 Mango 3400 2 5 Orange 1700
If i pass order id '1' i need record like
Orange Apple 5000 3000
If i pass order id '2' i need record like
Mango Orange 3400 1700
how can i achieve this can any one help for the same.
解决方案
Have you heard about Pivots[^]?
MSDN KB:
How to PIVOT Data Using T-SQL?[^]
Pivot tables in SQL Server. A simple sample.[^]
CP-QA KB:
How to Perform Pivot Operation in T-SQL..?[^]
Simple Way To Use Pivot In SQL Query[^]
SQL - Pivot with Grand Total Column and Row[^]
Declare @OrderId int = 2 If((select distinct 1 from Tem where Order_Id = @OrderId)=1) begin select Orange,Apple,Mango from (select Order_Id,ItemType,total From Tem Where Order_Id = @OrderId)u pivot(sum(total) for ItemType in ([Orange],[Apple],[Mango])) as pvt end
Use Dynamic Query with Pivot... try some thing like this...
Declare @Id Int, @Sql varchar(400),@Cols varchar(40) Create Table #Temp ( Order_Id Int, InventoryId Int, ItemType Varchar(40), total Int ) Insert into #Temp Values(1,5,'Orange',5000),(1,4,'Apple',3000),(2,1,'Mango',3400),(2,5,'Orange',1700) Set @Id=2 -- if you want records for id 1 set @Id=1 Set @Cols= Stuff((Select Distinct '],['+ItemType From #Temp Where Order_Id=@Id for Xml Path(''),Type).value('.','VARCHAR(Max)'), 1, 2,'')+']' Set @Sql= 'Select '+@Cols + 'From (Select ItemType,total From #Temp Where Order_Id=' + Cast(@Id as Varchar) +')st pivot (Sum(total) For ItemType in (' + @Cols + '))Pvt' Exec(@Sql) Drop Table #Temp
这篇关于在sql中将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文