Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create table to group th e people of same department Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 12:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:08 AM
Points: 68, Visits: 409
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???
Post #1434146
Posted Friday, March 22, 2013 1:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 23, 2014 12:06 AM
Points: 205, Visits: 251
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
Post #1434147
Posted Saturday, March 30, 2013 3:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,116, Visits: 6,438
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)
Post #1437249
Posted Monday, April 1, 2013 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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)
Post #1437446
Posted Tuesday, April 2, 2013 1:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:08 AM
Points: 68, Visits: 409
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.
Post #1437721
Posted Tuesday, April 2, 2013 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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)
Post #1437886
Posted Tuesday, April 2, 2013 11:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:08 AM
Points: 68, Visits: 409
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.

Post #1438172
Posted Wednesday, April 3, 2013 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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)
Post #1438345
Posted Wednesday, April 3, 2013 10:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:08 AM
Points: 68, Visits: 409
Thank you the query is working fine.....
regards
gurjer
Post #1438644
Posted Thursday, April 4, 2013 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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)
Post #1438787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse