How to change row into column in sql server

Question

2.33/5 (2 votes)

See more:

ASP.NET

SQL-Server

How to change row into column in sql server??

Posted 16-Dec-13 21:39pm

Ajay_pravin

Add a Solution

Comments

♥…ЯҠ…♥ 17-Dec-13 3:42am

What happened to Google? Can't you able to access it?

6 solutions

Solution 7

SQL

IF Less Columns are there we can use union also

EmpId	EmpName	DeptId	Salary	Gender	GenderName
13	Ash	2	8000	0	F
18	Renu	4	123	0	F
19	Raj	2	11000	1	M
20	Ramesh	5	25000	1	M


SELECT DISTINCT A.DeptId,SUM(A.M)AS M,SUM(A.F)AS F
FROM
(
Select  DeptId, Salary as M,0 as F From Employee Where Gender=1
Union
Select  DeptId,0 AS M, Salary as F From Employee Where Gender=0
)AS A
Group by A.DeptID


DeptId	M	F
2	11000	8000
4	0	123
5	25000	0

Renuka

Posted 16-Dec-13 23:58pm

Renuka Ruke

Updated 17-Dec-13 1:41am

v2

Comments

TrushnaK 20-Dec-13 0:20am

are you think it is right answer.

Renuka Ruke 20-Dec-13 1:26am

what problem in above example please explain me

TrushnaK 20-Dec-13 3:19am

suppose i have table like this:-
empid wagecode amount
1 basic 1000
1 TA 500
1 DA 500
2 Basic 1500
2 TA 750
2 DA 750

and i want result like this
empid basic TA DA
1 1000 500 500
2 1500 750 750

which is wagecode as column
how could you do that using union.

for this you required pivot. eg.
SELECT empid , [Basic], [TA],[DA]
FROM (
SELECT empid, wage, amount
FROM yourtable) up
PIVOT (SUM(amount) FOR wage IN (basic, ta,da)) AS pvt
ORDER BY empid
GO

or for reference visit this link:-
http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

Renuka Ruke 20-Dec-13 4:13am

SELECT DISTINCT A.empid,SUM(A.Basic)AS Basic ,SUM(A.TA)AS TA,SUM(A.DA)AS DA
FROM
(
Select empid, amount as [Basic],0 as TA,0 as DA From EmployeeSalary Where wagecode='Basic'
UNION
Select empid, 0 as [Basic],amount as TA,0 as DA From EmployeeSalary Where wagecode='TA'
UNION
Select empid, 0 as [Basic],0 as TA,amount as DA From EmployeeSalary Where wagecode='DA'
)AS A
Group by A.empid

TrushnaK 20-Dec-13 5:03am

see this example
username points
sam 1000
suthish 2000
kitan 1500
pankaj 4000
this table have 10000 record and i want this result

sam suthish kitan pankaj
1000 2000 1500 4000

then how many subqueries you have.

Renuka Ruke 20-Dec-13 5:16am

I already mentioned that IF Less Columns are there so we can use union ,I am just giving the idea for basic understanding ,i know there are many ways to convert rows to column this is one type

Solution 6

Your can use this function

SQL

 create function [dbo].[patrak3_ugc_data_msc]
(
	
)
RETURNS @ugc_patarakno3 TABLE(sr_no int identity(1,1),
	college_id int,
	subject_paper varchar(255),	
	male varchar(50),
	female varchar(50),
	subject_paper2 varchar(255),	
	male2 varchar(50),
	female2 varchar(50),
	subject_paper3 varchar(255),	
	male3 varchar(50),
	female3 varchar(50),
	subject_paper4 varchar(255),	
	male4 varchar(50),
	female4 varchar(50)
	
)
	
as 
begin

DECLARE @Subject_Paper varchar(255)
DECLARE @male INT
DECLARE @female INT
DECLARE @srno INT
DECLARE @college_id INT


DECLARE MYCUR CURSOR STATIC LOCAL READ_ONLY FOR
select 
sd.swd_college_id,
Row_Number()over(PARTITION  BY swd_college_id order by swd_college_id ) as srno,
sm.Subject_Paper, 
SUM(case when em.gender=1 then 1 else 0 end) as male,
SUM(case when em.gender=2 then 1 else 0 end) as female
from semester_wise_student_data as sd,

	OPEN MYCUR
	FETCH NEXT FROM MYCUR INTO @college_id,@srno,@Subject_Paper,@male,@female
	WHILE @@FETCH_STATUS =0
	BEGIN
		
	
	IF(@srno = 1)
	BEGIN
		insert into @ugc_patarakno3 (college_id,subject_paper,male,female)values(@college_id,@Subject_Paper,@male,@female)	
	end	
	
	IF(@srno = 2)
	BEGIN
		update @ugc_patarakno3 set subject_paper2=@Subject_Paper,male2=@male,female2=@female where college_id=@college_id
	END
	
	IF(@srno = 3)
	BEGIN
		update @ugc_patarakno3 set subject_paper3=@Subject_Paper,male3=@male,female3=@female where college_id=@college_id
	END
	
	IF(@srno = 4)
	BEGIN
		update @ugc_patarakno3 set subject_paper4=@Subject_Paper,male4=@male,female4=@female where college_id=@college_id
	END
	
	FETCH NEXT FROM MYCUR INTO @college_id,@srno,@Subject_Paper,@male,@female
	END 
	
	
	CLOSE MYCUR
	DEALLOCATE MYCUR

Return 
end

Posted 16-Dec-13 23:24pm

Rashmikants Monpara

Solution 4

refer below links:-
http://dba.stackexchange.com/questions/19057/convert-rows-to-columns-using-pivot-in-sql-server-when-columns-are-string-data[^]
http://blog.jontav.com/post/8344518585/convert-rows-to-columns-columns-to-rows-in-sql-server[^]

Posted 16-Dec-13 23:17pm

TrushnaK

Solution 3

Using PIVOT and UNPIVOT[^]
Pivoting data in SQL Server[^](Alternate ways)

Posted 16-Dec-13 23:03pm

thatraja

Comments

Maciej Los 17-Dec-13 5:06am

Very good links, but what about SearchBox (see my answer)?
5!

thatraja 17-Dec-13 5:15am

Poor Larry & Sergey! Still some developers not using that box for research. You could find my choclate there.

Solution 2

I would suggest to use SearchBox[^] on the right-top corner of this site. There are tons of examples!

Posted 16-Dec-13 22:16pm

Maciej Los

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)

Jain Nishant · Accepted Answer · 2013-12-16T21:48:00

Solution 1

Use Pivot for this.
See:- Simple Way To Use Pivot In SQL Query[^]

Posted 16-Dec-13 21:48pm

Jain Nishant

Comments

Maciej Los 17-Dec-13 4:17am

Good link, +5!
Please, see my answer ;)