urgent help needed :convert rows into columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

urgent help needed :convert rows into columns

Author

Topic

Theju
Starting Member

7 Posts

Posted - 2010-10-18 : 21:53:44

Hi all

I am new to SQL SERVER.
Please help me how to write a cursor/ procedure to convert rows into columns. I am using SQL SERVER 2000.

i am having 3 tables namely emp,dept,joiningdate.

emp table contains columns with values

empid, empname

1 Smith

2 john,

3 david

4 Ann

. .

.

.

.

20 stephen

dept table contains columns with values like

empid, deptid, dept name,value

1 100 Prod 1

2 101 dev 1

3 102 support 1

4 103 sales 1

. . .

. '

.

.

20 120 security 1

and year table contains

deptid, year

100 1900

101 1910

102 1920

103 1930

.

.

120 1950

Now using these 3 tables i want to write a querry to get the result like this.

empname 1900 1910 1920 1930.....................1950

Smith 1 0 0 0....................... 0

john 0 1 0 0....................... 0

david 0 0 1 0....................... 0

Ann 0 0 0 1........................0

Please help me out. Its a urgent requirement.

Thanks & regards

Thejesh Kumar
Application Developer

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 00:52:38

If you are new to SQL Server how could your seniors just hand you such a requirement?

Even if we give you a solution it's of no use for you to understand the logic behind the code.

Piece of advice :-

"DONT BE A COPY PASTE MASTER."

PBUH

Theju
Starting Member

7 Posts

Posted - 2010-10-19 : 02:00:25

Please help me out.

Thejesh Kumar
Application Developer

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 02:31:04

Ok.
Can you post the query where it brings all the related records from the three table?

PBUH

Theju
Starting Member

7 Posts

Posted - 2010-10-19 : 05:12:11

here is the querry

SELECT E.EMPNAME,D.VALUE,Y.JOININGYEAR FROM EMP E
LEFT JOIN (SELECT * FROM DEPT) D
ON E.EMPID=D.EMPID
INNER JOIN YEARS Y
ON D.DEPTID=Y.DEPTID

but i want the result as below. how to do this

empname 1900 1910 1920 1930.....................1950

Smith 1 0 0 0....................... 0

john 0 1 0 0....................... 0

david 0 0 1 0....................... 0

Ann 0 0 0 1........................0

Thejesh Kumar
Application Developer

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 05:19:30

Are the years fixed?
Will the minimum year always be 1900 & max 1950?

PBUH

kishore_pen
Starting Member

49 Posts

Posted - 2010-10-19 : 05:32:14

check the link(s):
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/831611f4-f05f-4467-8afe-865b0048b9ae
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000

Theju
Starting Member

7 Posts

Posted - 2010-10-19 : 05:47:58

No years are not fixed. I have just shown upto 1950 but Max value will be greater than 1950.

Thejesh Kumar
Application Developer

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 06:03:37

Have a look here

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

PBUH

Theju
Starting Member

7 Posts

Posted - 2010-10-19 : 08:26:17

Thanks a lot for the link. i did it using the crosstab procedure.

Thejesh Kumar
Application Developer

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 08:29:42

quote:
Originally posted by Theju

Thanks a lot for the link. i did it using the crosstab procedure.

Thejesh Kumar
Application Developer

Welcome.However you could had easily acheieved it had you used any reporting tool.

PBUH

Theju
Starting Member

7 Posts

Posted - 2010-10-19 : 09:05:12

No

Thejesh Kumar
Application Developer

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 09:10:29

quote:
Originally posted by Theju

No

Thejesh Kumar
Application Developer

Why????

PBUH

Theju
Starting Member

7 Posts

Posted - 2010-10-20 : 08:44:38

we are not using any reporting tools. v just export it to excel.

Thejesh Kumar
Application Developer

Piyush
Starting Member

8 Posts

Posted - 2011-07-25 : 01:59:54

I have put a complete article, in which you will find 3 methods using which the rows can be transformed to column attributes:

unspammed

So i will suggest you to go through this carefully!!

Piyush Bajaj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources