A query to get n values from one column

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

SQL Server Development (2000)

A query to get n values from one column

Author

Topic

vishalj
Starting Member

32 Posts

Posted - 2006-05-11 : 16:45:34

I have a query which join 5 tables say my result is

Warehouse ProductName Features Price Manf

I just need to make some analysis and need to select any 5 features from Features column for each productname. There can be 10 - 50 features mentioned in the table with features.

Say my result it like only for productname and features column
which come from diff tables. Here I am getting more than 5 features
I need to reduce that to 5 for each. So how do I do this.

Productname Features

AAAAA 12
AAAAA 145
AAAAA 23
AAAAA 34234
AAAAA 234234
AAAAA 32234
AAAAA 134234
AAAAA 21
AAAAA 14
AAAAA 556
BBBBB 19
BBBBB 23
BBBBB 6
BBBBB 1
BBBBB 2
BBBBB 11
BBBBB 15
BBBBB 12
BBBBB 6
BBBBB 4

Also very important this need to be done is how do i avoid repititions
and do the cross- tab join and make only one row for one product name
and get the result like this

Productname Features

AAAAA 12, 45, 234, 256, 25677, 24223........
BBBBB 12,121, 144, 58885, 8888, 99999.....
CCCCC 111, 144, 2333, 22333, 22221.......

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-11 : 16:54:29

I'm not clear about ur Question
Some hints:
For ur First Part of the Question : Use "TOP 5 " with "Group By "

For the Second Part (Rows to Columns) -->
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Srinika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-11 : 17:25:27

for 1st request see
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 2

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

vishalj
Starting Member

32 Posts

Posted - 2006-05-12 : 15:32:36

THis does not solve my FIRST part. I think I have to write a cursor to do that. but would that be the best way or if there is another wa i am not sure.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-12 : 21:38:40

I am not sure what you mean by 5 features (to limit the output to just 5 or have two records with 'AAA' because you have 10 features (10/5=2).)
For the first denormalization, here's my suggestion:
SET NOCOUNT ON
DROP TABLE WAREHOUSE
GO
DROP TABLE TEMPDB
GO
CREATE TABLE WAREHOUSE(
ProductName VARCHAR(10),
Features VARCHAR(20)
)
go

insert into warehouse values('AAAAA','12')
insert into warehouse values('AAAAA','145')
insert into warehouse values('AAAAA','23')
insert into warehouse values('AAAAA','34234')
insert into warehouse values('AAAAA','234234')
insert into warehouse values('AAAAA','32234')
insert into warehouse values('AAAAA','134234')
insert into warehouse values('AAAAA','21')
insert into warehouse values('AAAAA','14')
insert into warehouse values('AAAAA','556')
insert into warehouse values('BBBBB','19')
insert into warehouse values('BBBBB','23')
insert into warehouse values('BBBBB','6')
insert into warehouse values('BBBBB','1')
insert into warehouse values('BBBBB','2')
insert into warehouse values('BBBBB','11')
insert into warehouse values('BBBBB','15')
insert into warehouse values('BBBBB','12')
insert into warehouse values('BBBBB','6')
insert into warehouse values('BBBBB','4')
go

--GET THE DISTINCT LIST OF NAMES and CREATE THE DENORMALIZE TABLE
--MODIFY THIS QUERY IF YOU NEED TWO AAA, use the principle below
SELECT DISTINCT PRODUCTNAME, CAST('' AS VARCHAR(200)) Features
INTO TEMPDB
FROM WAREHOUSE
go
--use the hint and top so it will be much faster
WHILE EXISTS(
SELECT TOP 1 1
FROM TEMPDB T WITH (FASTFIRSTROW) JOIN WAREHOUSE W ON (T.ProductName = W.ProductName)
WHERE CHARINDEX(' ' + W.Features +',', T.Features) = 0
)
BEGIN
UPDATE t
SET Features = T.Features + ' ' + W.Features + ','
FROM TEMPDB T JOIN WAREHOUSE W ON (T.ProductName = W.ProductName)
WHERE
CHARINDEX(' ' + W.Features +',', T.Features) = 0
END
GO

--show the result
select *
from TEMPDB
ORDER BY PRODUCTNAME

drop table WAREHOUSE
go
DROP TABLE TEMPDB
GO
--result:
--productname features
--AAAAA 556, 14, 21, 134234, 32234, 234234, 34234, 23, 145, 12,
--BBBBB 4, 6, 12, 15, 11, 2, 1, 23, 19,

May the Almighty God bless us all!

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-12 : 21:41:08

The worst runtime will be of Q(n) where the n is the highest count of features for a product.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources