Multiple rows into one query result row...

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)

Multiple rows into one query result row...

Author

Topic

EOS3
Starting Member

3 Posts

Posted - 2005-01-24 : 15:09:47

I have an abstract relational database underneath some business objects - for instance, say I have two tables...

TABLE 1: A simple list of people...

ID USER
---------
1 Mike
2 John

TABLE 2: Name/Value pairs of attributes linked to table 1 by ID...

ID NAME VALUE
-------------------
1 Hair Brown
1 Eyes Blue
2 Weight 200

So you can see from this that Mike has brown hair and blue eyes, and that John weighs 200 lbs.

I want a query that selects a person and all their attributes (whatever they may be), and returns the results on one row like this (when run with a WHERE clause that selects user Mike).

USER HAIR EYES
-------------------
Mike Brown Blue

And returns this when run with a WHERE clause that selects user John...

USER WEIGHT
---------------
John 200

Any ideas? Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 15:19:31

Is this a homework assignment? As we don't answer homework questions, the only hint that I'll give is, you need to look up JOIN in SQL Server Books Online. JOIN is what you need to solve this.

Tara

jhermiz

3564 Posts

Posted - 2005-01-24 : 15:23:28

quote:
Originally posted by EOS3

I have an abstract relational database underneath some business objects - for instance, say I have two tables...

TABLE 1: A simple list of people...

ID USER
---------
1 Mike
2 John

TABLE 2: Name/Value pairs of attributes linked to table 1 by ID...

ID NAME VALUE
-------------------
1 Hair Brown
1 Eyes Blue
2 Weight 200

So you can see from this that Mike has brown hair and blue eyes, and that John weighs 200 lbs.

I want a query that selects a person and all their attributes (whatever they may be), and returns the results on one row like this (when run with a WHERE clause that selects user Mike).

USER HAIR EYES
-------------------
Mike Brown Blue

And returns this when run with a WHERE clause that selects user John...

USER WEIGHT
---------------
John 200

Any ideas? Thanks in advance!

SELECT t.ID, t.User, t2.Name, t2.Value FROM USERS t INNER JOIN ATTRIBUTES t2 ON t2.ID=t.ID ORDER BY t2.Name

This is a parent child relationship, so what you might want to do in the presentation layer is have it so that the user is selected and then show all attributes for that user.

Is this what you are looking for ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 15:27:37

Jon, please don't answer home questions. It smells suspiciously of homework. They'll never learn if we just answer these. Even if this one isn't a homework question, it's a basic question, so we'd be better off giving them hints so that they can learn rather than providing the solution.

Tara

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-24 : 15:38:20

Tara, you gotta give the guy some credit for finding the right place to get someone to do his homework.

OES3, This will get you an A+:


SQL Server Books Online
  Accessing and Changing Relational Data
    Query Fundamentals

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-24 : 15:46:04

Well I gotta ask this...while this is flexible, the structure is a pain...is this considered a higher form of normalization?

I wouldn't suppose so...such that the name field would also be a property of the key...such that if all attributes were moved then you would only need 1 table, with the "key" nad their properties....

Brett

8-)

EOS3
Starting Member

3 Posts

Posted - 2005-01-24 : 15:51:36

You guys are funny...

No, it's been several years since I did my "homework assignments" in comp sci...

The issue is this...

When I join in this manner:

SELECT table1.user, table2.name, table2.value
FROM table1
JOIN table2 on table1.id = table2.id
WHERE table1.id = '1'

I get multiple rows for each user, like this...

USER NAME VALUE
-----------------
Mike Hair Brown
Mike Eyes Blue

when what I want is

USER HAIR EYES
Mike Brown Blue

Understand?

jhermiz

3564 Posts

Posted - 2005-01-24 : 15:54:37

quote:
Originally posted by EOS3

You guys are funny...

No, it's been several years since I did my "homework assignments" in comp sci...

The issue is this...

When I join in this manner:

SELECT table1.user, table2.name, table2.value
FROM table1
JOIN table2 on table1.id = table2.id
WHERE table1.id = '1'

I get multiple rows for each user, like this...

USER NAME VALUE
-----------------
Mike Hair Brown
Mike Eyes Blue

when what I want is

USER HAIR EYES
Mike Brown Blue

Understand?

I love the blunt "understand"...no we are idiots

. Tara meant no harm, she was looking out for you

.

The structure is painful, but yes it is possible. Look up cross tab queries :)....just remember (from the old comp sci days)..as x approaches to infinity..so do the number of columns returned per row :).

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

EOS3
Starting Member

3 Posts

Posted - 2005-01-24 : 15:58:41

X002548,

I wouldn't say this is a higher form of normalization. It is an abstract data model fit under a set of business objects I wrote in C#. The need was to have a flexible class that could have user-definable attributes.

Thus, the name/value pair. Now, I need to return all of an objects attributes on one line for use in a report generator (like Crystal). Yes, I know I could use subreports in the report generator, but I was hoping for an answer from SELECT statement...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 16:04:16

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

Tara

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-24 : 17:43:53

[code]
OES3,
I feel a little guilty about the homework crack so here's a dynamic
crosstab statement for your table structure. You can just change
the @ID value to return John or Mike's attributes as columns.
TG

if Object_id('tempdb.dbo.#User') > 0
Drop table #User
if Object_id('tempdb.dbo.#Attrib') > 0
Drop table #Attrib

GO
Create Table #User
([ID] int
,[User] varchar(15))

create Table #Attrib
([ID] int
,[AttName] varChar(15)
,[AttValue] varChar(15))
GO

set nocount on
insert #User
Select 1, 'Mike' UNION ALL
Select 2, 'John'
insert #Attrib
Select 1, 'Hair', 'Brown' UNION ALL
Select 1, 'Eyes', 'Blue' UNION ALL
Select 2, 'Weight', '200'

--==============================================================

declare @ID varChar(12)
Set @ID = '1' --'2'

--============

declare @agg varchar(8000)
,@case varChar(8000)
,@Sql varchar(8000)
,@i int
,@AttName varChar(15)
set @i = 1
declare crs cursor for
Select distinct AttName from #Attrib where ID = @ID
open crs
Fetch next from crs into @AttName

if @@Fetch_status = 0
Begin
Select @agg = ''
,@case = ''
End
while @@Fetch_status = 0
Begin
select @agg = @agg + ',' + @AttName + ' = max(' + @AttName + ')'
,@case = @case + char(13) + replicate(char(9),2) + ',' + @AttName + ' = case when AttName = ''' + @AttName + ''' then AttValue else null end'
,@i = @i + 1

Fetch next from crs into @AttName
End
close crs
deallocate crs

set @Sql = '
Select u.[User] ' + @agg + '

From #User u
JOIN (
SElect [ID]
' + @case + '
From #Attrib
Where [ID] = ' + @ID + '
) as tg ON u.[ID] = tg.[ID]
Group by u.[User]
'

--PRINT @Sql
--PRINT ''

set ansi_warnings off
exec(@Sql)
set ansi_warnings on
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources