Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create table to group th e people of same department


create table to group th e people of same department

Author
Message
super48
super48
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 500
NAME AGE SALARY DEPARTMENT
SHARATH 24 21000 MARKETING
RATHAN 35 21000 MARKETING
RATS 28 21000 IT
DARSHAN 23 20000 IT
LINI 25 25000 IT
SHAN 23 20000 MARKETING
SUDARSHAN 22 20000 IT

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???
Horatiu
Horatiu
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 301
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
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 6490
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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
super48
super48
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 500
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
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
super48
super48
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 500
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
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
super48
super48
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 500
Thank you the query is working fine.....
regards
gurjer
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search