create table to group th e people of same department

Question

create table to group th e people of same department

super48

SSCrazy

Points: 2682
More actions
March 22, 2013 at 12:42 am

#275530

NAMEAGESALARYDEPARTMENT
SHARATH2421000MARKETING
RATHAN3521000MARKETING
RATS2821000IT
DARSHAN2320000IT
LINI2525000IT
SHAN2320000MARKETING
SUDARSHAN2220000IT
to above given table i wan to write a qury that display
IT MARKETING
RATS RATHAN
DARSHAN SHARATH
LINI SHAN
SUDARSHAN
HOW TO DO IT???

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Horatiu SSChasing Mays Points: 652 More actions · Answer 1

with it as (select row_number() over (order by name) as row_num, name, department from #test where department = 'IT')

,marketing as (select row_number() over (order by name) as row_num, name, department from #test where department = 'MARKETING')

select i.name as NameIt,m.name as NameMarketing from it i full outer join marketing m on i.row_num = m.row_num

If you have more departments you can try to generate a dynamic script based on what i gave you.

There is always something new to learn.
My personal SQL Blog[/url]

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

Dear Gurjer,

Honestly, from the description of your problem I have no idea what you are trying to achieve. Please give us some DDL statements, i.e. how to create your tables, some test data in a readily consumable format, and then please tell us what you expect the query you are trying to write to do to the test data you supplied to arrive at the desired result. Because, honestly, from what you posted, I have not a clue.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

I was feeling charitable this morning so I created the ddl and sample data to work with.

if OBJECT_ID('tempdb..#Data') is not null

drop table #Data

create table #Data

(

MyName varchar(20),

Age int,

Salary int,

Department varchar(20)

)

insert #Data

select 'SHARATH', 24, 21000, 'MARKETING' union all

select 'RATHAN', 35, 21000, 'MARKETING' union all

select 'RATS', 28, 21000, 'IT' union all

select 'DARSHAN', 23, 20000, 'IT' union all

select 'LINI', 25, 25000, 'IT' union all

select 'SHAN', 23, 20000, 'MARKETING' union all

select 'SUDARSHAN', 22, 20000, 'IT'

select * from #Data

If the OP can confirm this will work we can figure out what they want as output and proceed from there.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

super48 SSCrazy Points: 2682 More actions · Answer 4

WHAT THE TABLE CREATED IS I HAVE ...I JUST WANT TO

DISPLAY

IT,MARKETING COLUMNS UNDER WHICH THE PERSONS NAME BELONGING TO THAT DEPARTMENT SHOULD COME...

GROUPING PERSONS UNDER SAME DEPARTMENT.

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

gurjer48 (4/2/2013)
WHAT THE TABLE CREATED IS I HAVE ...I JUST WANT TO
DISPLAY
IT,MARKETING COLUMNS UNDER WHICH THE PERSONS NAME BELONGING TO THAT DEPARTMENT SHOULD COME...
GROUPING PERSONS UNDER SAME DEPARTMENT.

First of all please turn off the caps lock. All caps is considered yelling and it is difficult to read.

The problem we have here is that we don't know your project, we don't know your data structures, we don't know what you are trying to do and we can't see your screen.

Is the data that I posted an accurate depiction? If so, what should the output look like? I am willing to help but you have to help me understand what the problem is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

super48 SSCrazy Points: 2682 More actions · Answer 6

yes the table you created is correct....

i want to display 2 columns in the o/p IT,Marketing

1.

IT

darshan

lime

rats

sudarshan

2.

Marketing

rathan

shan

sharath

in the table you created dived the persons base on the department they belong to..

and sorry for capitals.

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

This should work based on the sample data.

;with Marketing as

(

select *, ROW_NUMBER() over (order by MyName) as RowNum

from #Data

where Department = 'Marketing'

)

, IT as

(

select *, ROW_NUMBER() over (order by MyName) as RowNum

from #Data

where Department = 'IT'

)

select m.MyName as Marketing, IT.MyName as IT

from Marketing m

full outer join IT on m.RowNum = IT.RowNum

Let me know if that works for you.

Sorry about complaining about your capitals. Must have been in a bad mood yesterday when I posted that. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

super48 SSCrazy Points: 2682 More actions · Answer 8

Thank you the query is working fine.....

regards

gurjer

Sean Lange SSC Guru Points: 286609 More actions · Answer 9

gurjer48 (4/3/2013)
Thank you the query is working fine.....
regards
gurjer

Glad to know it is working and thanks for the feedback.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/