Using rows as columns in a table.

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

SQL Server 2000 Forums

SQL Server Development (2000)

Using rows as columns in a table.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-01-22 : 08:32:55

Hi to all the SQL Gurus in this forum,
I have a table named LeaveTypes which contains a column named Leavetype where the Leavetype are entered by the user ex Maternity leave,Causual leave,Emergency leaves etc.User can add different types of leaves in that column.
What I want is that I want to to have those Leavtype as a column names in a temporay table.
i.e Suppose I name the temporary table as tbl I want that this table should have the column numbers equal to that of the number of leavetypes entered by the user in the table Leavetypes.
ex if I have Casual Leaves & Sick Leaves entered by the user the temporay table should contain the column names as Casual Leaves & Sick leaves.
If he enters just Casual Leaves the temporary table should contain only one column named as Casual Leaves.
Thanks for your time.
Any help will be greatly appreciated.

P.S--khtan & Chirag please help me out as you have helped me lot of times in the past.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 08:40:26

1. to create temporary table with data from table as column name you will need make use of dynamic sql. But why do you want to do this ?

Are you looking for pivot table / cross tab query ?

You can do something like :


select user_id, 
       [Maternity Leave] = sum(case when Leavetype = 'Maternity Leave' then 1 else 0end),
       [Casual Leave] = sum(case when Leavetype = 'Casual Leave' then 1 else 0 end),
       [Emergency Leave] = sum(case when Leavetype = 'Emergency Leave' then 1 else 0 end)       
from   tbl1
group by user_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 08:45:08

Thanks, that leaves us other with plenty of time

Peter Larsson
Helsingborg, Sweden

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-01-22 : 09:00:08

Great it worked.Thanks khtan once again.
This is not exactly what I want but I got the basic idea on how to get what I need & how to go about it.
Peso no hard feelings but I never got any replies from your 5655 posts that you posted.
Khtan thank you once again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 09:03:27

Sure.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=68320

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 09:05:42

You are welcome.

I had to answer this it mention my name

ayamas, next time you just list the top 40 names from http://www.sqlteam.com/forums/members.asp.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-01-22 : 09:15:46

quote:
Originally posted by Peso

Sure.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=68320

Peter Larsson
Helsingborg, Sweden

OOPS...!Sorry Peso.You did reply to one of my posts.I take my word back.
Thanks to all you SQL Gurus wish I could be like you one day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources