Query processing - SQL Server Forums

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

Transact-SQL (2000)

Query processing

Author

Topic

hansalas
Starting Member

14 Posts

Posted - 2006-08-21 : 02:16:40

Hi guys,
Need some help in some query processing...

Here goes,this is the results i have obtained so far
|id |cat | name |
1 .....a... apple
1 .....b .. banana
2 .....a ...austria
2 .....b ...brazil
2 .....c ...china
3 .....a ...abraham
3 .....c ...clinton

Column cat can have at most 3 different types of values..{a,b,c}

I wanna write a query such that the result comes out as such
<(....id ......a .......b......... c..) corresponding to these fields.>

|Col 1| Col 2 | Col 3 | Col 4|
....1 ..apple ...banana
....2 ..austria..brazil..china
....3 ..abraham .........clinton

Anyone can help...thanks guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 02:26:32

[code]-- prepare test data
declare @table table (id int, cat char, name varchar(10))

insert @table
select 1, 'a', 'apple' union all
select 1, 'b', 'banana' union all
select 2, 'a', 'austria' union all
select 2, 'b', 'brazil' union all
select 2, 'c', 'china' union all
select 3, 'a', 'abraham' union all
select 3, 'c', 'clinton'

-- do the work
SELECT ID Col1,
MAX(CASE WHEN cat = 'a' THEN name ELSE '' END) Col2,
MAX(CASE WHEN cat = 'b' THEN name ELSE '' END) Col3,
MAX(CASE WHEN cat = 'c' THEN name ELSE '' END) Col4
FROM @Table
GROUP BY ID
ORDER BY ID[/code]
Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 05:34:37

Where do you want to show data?
For multiple cats, you can try this logic with some changes

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

hansalas
Starting Member

14 Posts

Posted - 2006-08-21 : 21:29:51

Hi thanks guys...Peso and madhivanan for the help...

Btw, i have another question ..hope you guys can help me out..
Here goes,
Using Pubs,

(select 'City Area' as city,'State Territory' as state)
union all
(select city,state from authors order by state)

I am getting an error...
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'order'.

What I am trying to do:
I am basically trying to run this query for a website..so i am sending in the headers as 'City Area' and 'State Territory' out..But I wanna sort them as state in the query below.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-21 : 21:43:46

you can't use ORDER BY in subquery with union.


Use pubs

-- this is not ok
(select 'City Area' as city,'State Territory' as state)
union all
(select city,state from authors order by state)

-- this is OK
(select 'City Area' as city,'State Territory' as state)
union all
(select city, state from authors) order by state

-- Without subquery. this is also ok. 
select 'City Area' as city,'State Territory' as state
union all
select city, state from authors order by state

hansalas
Starting Member

14 Posts

Posted - 2006-08-21 : 22:17:30

Hi khtan,
Agree with your views..
But how do u suggest i rewrite the query then...

My table shld be like this...
----[city]-----|---[state]
----City Area--|---State Territory
----Menlo Park-|---CA
----Gary-------|---IN

and so forth... The first row should not be sorted.. and the rest should be sorted by order...

Thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-21 : 22:36:31

why do you need the result header to be return as a record ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-21 : 22:40:12

[code]select *
from
(
select 'City Area' as city,'State Territory' as state
union all
select city, state from authors
) a
order by case when state = 'State Territory' then 0 else 1 end, state, city[/code]

hansalas
Starting Member

14 Posts

Posted - 2006-08-22 : 04:20:04

Sorry for the late reply...
Was at an horrendousely long meeting..

Other than that,Thanks for the solution...Werks gud..

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources