sql server programming: INTERESTING QUERY -- sql server programming

Re: INTERESTING QUERY

Phil
9/24/2003 2:02:24 AM

Thanks for the reply on this, it worked brilliantly.

Thanks Again Phil
[quoted text, click to view]

Re: INTERESTING QUERY

Uri Dimant
9/24/2003 9:44:56 AM

Phil
It will help you to start
CREATE TABLE #W
(
COL CHAR(1) NOT NULL,
COL1 CHAR(3) NOT NULL
)
INSERT INTO #W VALUES ('A','AAA')
INSERT INTO #W VALUES ('A','BBB')
INSERT INTO #W VALUES ('A','CCC')

INSERT INTO #W VALUES ('B','AAA')
INSERT INTO #W VALUES ('B','BBB')
INSERT INTO #W VALUES ('B','CCC')

SELECT COL,MAX(CASE WHEN COL1= 'AAA' THEN COL1 END) 'P',
MAX(CASE WHEN COL1= 'BBB' THEN COL1 END) 'P1',
MAX(CASE WHEN COL1= 'CCC' THEN COL1 END) 'P2'
FROM #W GROUP BY COL

DROP TABLE #W

[quoted text, click to view]

Re: INTERESTING QUERY

Vishal Parkar
9/24/2003 12:08:55 PM

can u post the complete table structure and let me know if each name will always have only 3 or
less than 3 papers.

--
- Vishal

Re: INTERESTING QUERY

Vishal Parkar
9/24/2003 12:27:13 PM

drop table #t
create table #t (name varchar(4000), paper varchar(4000))
insert into #t
select 'Buggs Bunny', 'Mirror' union all
select 'Buggs Bunny', 'Daily Mail' union all
select 'Buggs Bunny', 'The Star' union all
select 'Taz', 'The Observer' union all
select 'Taz', 'Daily Mail' union all
select 'Taz', 'The Standard'

Assumption:
1) i assume you will have only 3 or less than 3 papers belonging to one particular name
2) if you do not have a unique key in the table then create another temporary table with the unique
key.
as

select identity(int,1,1) as idd, name, paper into #tt from #t
if you do have a unique column inside table you can replace that with idd in following query.

--your query would look like

select name, (select paper from #tt b where name = a.name and
idd in(select min(idd) from #tt c where c.name = a.name) ) 'Paper1',
(select paper from #tt b where name = a.name and
idd in(select (min(idd) + 1) from #tt c where c.name = a.name) ) 'Paper2',
(select paper from #tt b where name = a.name and
idd in(select (min(idd) + 2) from #tt c where c.name = a.name) ) 'Paper3'
from
(Select distinct name from #tt) a

if above assumption fails you will have to build crosstab report , refer to this url.

http://www.sqlteam.com/item.asp?ItemID=2955

--
- Vishal

Re: INTERESTING QUERY

Louis Davidson
9/24/2003 10:56:47 PM

Yeah, this will perform much better than the other example, since this will
only resolve into one pass through the set. The other will do a lot more
work. Good post.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.