我想将行传输到Column SQL Server [英] I want to transfer rows to Column SQL Server
本文介绍了我想将行传输到Column SQL Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的表
Item_ID项目category_name余额branch_name
------------------ -------------------------------------------------- -----------------
PD-0000002DR ABC Drink 30000 Sensok
PD-0000002DR ABC Drink 333333.04总部
PD-0000002DR ABC Drink 60000 Angsnoul
PD-0000003PP书籍纸200000 Sensok
PD-0000005PP Pen Paper 23000总部
PD-0000006PP Pencel Paper 39819.82总部
i想要转移它:
Item_ID项目category_name总部Sensok Angsnoul
--------- -------------------------------------------------- ------------------------
PD-0000002DR ABC Drink 333333.04 30000 60000
PD-0000003PP Books Paper null 200000 null
PD-0000005PP Pen Paper 23000 null null
PD-0000006PP Pencel Paper 39819.82 null null
解决方案
检查:< br $> b $ b
创建 table #table1(Item_ID nvarchar (max),Item nvarchar ( max),category_name nvarchar (max),余额 float ,branch_name nvarchar (max))
插入 进入#table1 (Item_ID,Item,category_name,balance,branch_name) values (' PD-0000002DR',' ABC',' Drink', 30000 ,' Sensok')
insert into #table1(Item_ID,Item,category_name,balance,branch_name)值(' PD-0000002DR',' ABC',' 喝', 333333 。 04 ,' 总公司')
insert into #table1(Item_ID,Item,category_name,balance,branch_name) values (' PD-0000002DR',' ABC' ,' 饮料', 60000 ,< span class =code-string>' Angsnoul')
insert 进入#table1(Item_ID,Item,category_name,balance,branch_name) values (' PD-0000003PP',' Books',' 论文', 200000 ,' Sensok')
insert into #table1(Item_ID,Item,category_name,balance,branch_name) values (' PD-0000005PP',' Pen',' Paper', 23000 ,' 总公司')
插入 进入#table1(Item_ID,Item,category_name,balance,branch_name)值(' PD-0000006PP', ' Pencel',' Paper', 39819 。 82 ,' 总公司')
选择 * 来自#table1
SELECT Item_ID,Item,category_name,[总公司],[Sensok ],[Angsnoul]
FROM (
SELECT Item_ID,Item,category_name, balance,branch_name
FROM #table1)up
PIVOT(SUM(余额) FOR branch_name IN ([总公司],[Angsnoul],[Sensok])) AS pvt
请仔细阅读这篇文章,
http:// blog。 sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ [ ^ ]
了解在SQL查询中使用Pivot的简单方法 [ ^ ]
This is my table
Item_ID Item category_name balance branch_name
-------------------------------------------------------------------------------------
PD-0000002DR ABC Drink 30000 Sensok
PD-0000002DR ABC Drink 333333.04 Head Office
PD-0000002DR ABC Drink 60000 Angsnoul
PD-0000003PP Books Paper 200000 Sensok
PD-0000005PP Pen Paper 23000 Head Office
PD-0000006PP Pencel Paper 39819.82 Head Office
i want transfer it like that :
Item_ID Item category_name Head Office Sensok Angsnoul
-----------------------------------------------------------------------------------
PD-0000002DR ABC Drink 333333.04 30000 60000
PD-0000003PP Books Paper null 200000 null
PD-0000005PP Pen Paper 23000 null null
PD-0000006PP Pencel Paper 39819.82 null null
解决方案
Check this:
create table #table1(Item_ID nvarchar(max),Item nvarchar(max),category_name nvarchar(max),balance float,branch_name nvarchar(max)) insert into #table1(Item_ID,Item,category_name,balance,branch_name) values('PD-0000002DR','ABC','Drink',30000,'Sensok') insert into #table1(Item_ID,Item,category_name,balance,branch_name) values('PD-0000002DR','ABC','Drink',333333.04,'Head Office') insert into #table1(Item_ID,Item,category_name,balance,branch_name) values('PD-0000002DR','ABC','Drink',60000,'Angsnoul') insert into #table1(Item_ID,Item,category_name,balance,branch_name) values('PD-0000003PP','Books','Paper',200000,'Sensok') insert into #table1(Item_ID,Item,category_name,balance,branch_name) values('PD-0000005PP','Pen','Paper',23000,'Head Office') insert into #table1(Item_ID,Item,category_name,balance,branch_name) values('PD-0000006PP','Pencel','Paper',39819.82,'Head Office') select * from #table1 SELECT Item_ID, Item, category_name,[Head Office], [Sensok], [Angsnoul] FROM ( SELECT Item_ID, Item, category_name,balance,branch_name FROM #table1) up PIVOT (SUM(balance) FOR branch_name IN ([Head Office], [Angsnoul], [Sensok])) AS pvt
Kindly go through this article,
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]
Learn the Simple Way To Use Pivot In SQL Query[^]
这篇关于我想将行传输到Column SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文