I want to transfer rows to Column SQL Server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

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

Posted 21-Jan-15 14:03pm

phanithly

Updated 21-Jan-15 15:57pm

DamithSL

v2

Add a Solution

2 solutions

Solution 1

Learn the Simple Way To Use Pivot In SQL Query[^]

Posted 21-Jan-15 15:09pm

Peter Leow

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajesh waran · Accepted Answer · 2015-01-22T02:16:00

Check this:

SQL

 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/[^]