Get data from different rows into same results 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 2005 Forums

Transact-SQL (2005)

Get data from different rows into same results row

Author

Topic

capella07
Starting Member

46 Posts

Posted - 2007-02-28 : 12:19:44

Hi, all

Okay, I'm in a situation where I know what I need to accomplish, but I'm struggling with how to explain it! So, it may be easiest if I try to do it visually. Looking at the post preview - it doesn't look pretty, but I can't figure out how to set up a table-looking structure within this message writing area - apparently multiple spaces in the message-writing area are translated back to single spaces for display. Do your best to try to decipher what I wrote - sorry:

This is the basic structure of the SQL table that I'm pulling the data from:

UID Field_Name Value_1 Value_2
1 City Chicago (Empty)
1 County Orange (Empty)
1 Price 000000 100000
1 Type Res (Empty)
2 City New York (Empty)
2 County Hudson (Empty)
2 Price 500000 10000000
2 Type Comm (Empty)
3 City Miami (Empty)
3 County Dade (Empty)
3 Price 500000 150000
3 Type Res (Empty)

And I want the results table to be organized like so:

UID City County Price Low Price High Type
1 Chicago Orange 000000 10000000 Res
2 New York Hudson 500000 10000000 Comm
3 Miami Dade 500000 15000000 Res

So I need to figure out how to set up my query so that it takes each UID's city, county, price (low and high in the same row), and type data and puts all of that into one row in the result set.

How the heck do I do that?

Thanks for anyone's help!

f u cn rd ths, u cn gt a gd jb n prgrmng

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-02-28 : 12:52:00

Here is 'a' way to do it. I'm having a brain issue today, so if I remember one of the better ways to do this I'll post later. But, for now here is something that works:

DECLARE @Tmp TABLE(UID INT, FieldName VARCHAR(100), Value1 VARCHAR(100), Value2 VARCHAR(100))

INSERT @Tmp
SELECT 1, 'City', 'Chicago', NULL UNION ALL
SELECT 1, 'County', 'Orange', NULL UNION ALL
SELECT 1, 'Price', '000000', '100000' UNION ALL
SELECT 1, 'Type', 'Res', NULL UNION ALL
SELECT 2, 'City', 'New York', NULL UNION ALL
SELECT 2, 'County', 'Hudson', NULL UNION ALL
SELECT 2, 'Price', '500000', '10000000' UNION ALL 
SELECT 2, 'Type', 'Comm', NULL UNION ALL
SELECT 3, 'City', 'Miami', NULL UNION ALL
SELECT 3, 'County', 'Dade', NULL UNION ALL
SELECT 3, 'Price', '500000', '150000' UNION ALL
SELECT 3, 'Type', 'Res', NULL

SELECT
	UID,
	(SELECT TOP 1 Value1 FROM @Tmp WHERE FieldName = 'City' AND t.UID = UID) AS City,
	(SELECT TOP 1 Value1 FROM @Tmp WHERE FieldName = 'County' AND t.UID = UID) AS County,
	(SELECT TOP 1 Value1 FROM @Tmp WHERE FieldName = 'Price' AND t.UID = UID) AS [Price Low],
	(SELECT TOP 1 Value2 FROM @Tmp WHERE FieldName = 'Price' AND t.UID = UID) AS [Price High],
	(SELECT TOP 1 Value1 FROM @Tmp WHERE FieldName = 'Type' AND t.UID = UID) AS [Type]
FROM 
	@Tmp t
GROUP BY 
	UID

-Ryan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-02-28 : 13:14:18

Also, here is more set based way which I think would be faster depending on set size:

SELECT DISTINCT
	t.UID,
	City,
	County,
	[Price Low], 
	[Price High],
	[Type]
FROM
	@Tmp t
INNER JOIN
	(
		SELECT UID, Value1 AS City
		FROM @Tmp
		WHERE FieldName = 'City'
	) tCity
	ON t.UID = tCity.UID
INNER JOIN
	(
		SELECT UID, Value1 AS County
		FROM @Tmp
		WHERE FieldName = 'County'
	) tCounty
	ON t.UID = tCounty.UID
INNER JOIN
	(
		SELECT UID, CAST(Value1 AS MONEY) AS [Price Low], CAST(Value2 AS MONEY) AS [Price High]
		FROM @Tmp
		WHERE FieldName = 'Price'
	) tPrice
	ON t.UID = tPrice.UID
INNER JOIN
	(
		SELECT UID, Value1 AS [Type]
		FROM @Tmp
		WHERE FieldName = 'Type'
	) tType
	ON t.UID = tType.UID

-Ryan

capella07
Starting Member

46 Posts

Posted - 2007-02-28 : 13:21:28

Thanks for the suggestion, Lamprey.

I took your code (from your first reply) and popped it in to sql server to run it and apply it to my situation. I'm assuming you added the temp table for the purposes of this forum. What I did was take the SELECT statement (with the nested SELECTs - I figured the solution would have something to do with that) and changed it to work with the table (called "preferences") I'm building the query for.

Here's the copy/pasted code from the query:

SELECT
public_user_id,
(SELECT TOP 1 Field_Value_1 FROM preferences WHERE Field_Name = 'City') AS City,
(SELECT TOP 1 Field_Value_1 FROM preferences WHERE Field_Name = 'County') AS County,
(SELECT TOP 1 Field_Value_1 FROM preferences WHERE Field_Name = 'current_price') AS [Price Low],
(SELECT TOP 1 Field_Value_2 FROM preferences WHERE Field_Name = 'current_price') AS [Price High],
(SELECT TOP 1 Field_Value_1 FROM preferences WHERE Field_Name = 'Type') AS [Type]
FROM
preferences
GROUP BY
public_user_id

The results structure is what I was looking for, but then it's doing something odd: There are currently 30 different 'public_user_id's (remember, each public_user_id has four rows in the sql table). The results return 30 rows, but all 30 rows have only the information from the first public_user_id - it's just repeated however many different public_user_ids there are.

Not sure what to make of - or do with - that...

What would you suggest?

Thanks for the help!

f u cn rd ths, u cn gt a gd jb n prgrmng

capella07
Starting Member

46 Posts

Posted - 2007-02-28 : 13:34:14

Update:

Okay, I added the keyword "DISTINCT" between the first SELECT and public_user_id, which gave me the correct values from the public_user_id column (different one for each line). But I'm still getting the values for the first public_user_id repeating throughout the rest of the results table.

Having had success with that, I tried adding DISTINCT in the other nested SELECTs, but sql didn't care for that too much! (But I'm sure you could've told me that!)

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 13:52:16

Here's a more SQL Server 2005 specific way to do it (uses the new PIVOT operator)

SELECT PT.UID, City, County
    , PT.Price [Price Low], MT.Value_2 [Price High], [Type]
FROM (SELECT UID, Field_Name, Value_1 FROM MyTable) as TS
PIVOT
(min(Value_1)
FOR Field_Name
IN ([City], [County], [Price], [Type])) as PT
INNER JOIN MyTable MT ON PT.UID = MT.UID AND MT.Field_Name = 'Price'

Note that your data design is not a good idea, it may seem flexible now but you'll regret it going forward and these kinds of queries will get more complex and perform badly when you have more data. You should rather create a fully normalized design.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 13:58:50

This seem easier...

-- prepare sample data
declare @t table (UID int, Field_Name varchar(20), Value_1 varchar(20), Value_2 varchar(20))

insert	@t
select	1, 'City', 'Chicago', null union all
select	1, 'County', 'Orange', null union all
select	1, 'Price', '000000', '100000' union all
select	1, 'Type', 'Res', null union all
select	2, 'City', 'New York', null union all
select	2, 'County', 'Hudson', null union all
select	2, 'Price', '500000', '10000000' union all
select	2, 'Type', 'Comm', null union all
select	3, 'City', 'Miami', null union all
select	3, 'County', 'Dade', null union all
select	3, 'Price', '500000', '150000' union all
select	3, 'Type', 'Res', null

-- Show the expected output
SELECT		UID,
		MAX(CASE WHEN Field_Name = 'City' THEN Value_1 END) AS City,
		MAX(CASE WHEN Field_Name = 'County' THEN Value_1 END) AS County,
		MAX(CASE WHEN Field_Name = 'Price' THEN Value_1 END) AS [Price low],
		MAX(CASE WHEN Field_Name = 'Price' THEN Value_2 END) AS [Price high],
		MAX(CASE WHEN Field_Name = 'Type' THEN Value_1 END) AS [Type]
FROM		@t
GROUP BY	UID
ORDER BY	UID

Peter Larsson
Helsingborg, Sweden

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-02-28 : 14:02:32

I think the issue you are having is that you left out part of the WHERE clause: t.UID = UID. This should correct your results. But, I'd probably go with snSQL's way as it is more 2005 centric.

Cheers,

-Ryan

capella07
Starting Member

46 Posts

Posted - 2007-02-28 : 14:05:42

snSQL,

Thanks for your reply. It looked like you used the actual fields, etc from my table, so I just copy/pasted your code into a query and hit 'check syntax'. I got this error.

Msg 325, Level 15, State 1, Line 4
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

Couple of things: this table is in a SQL Server 2000 database. Also, believe me, though I am relatively new to t-sql coding, I do know enough to see that this table structure is not necessarily the optimal way to put it together. Problem is, I can't change it, so I've got to figure out a way to make it work for me!

Anyway, back to your suggestion. Judging by the error and your point that this is a SQL Server 2005-specific solution, this probably isn't the best way to go, unfortunately.

Any other ideas?

Thanks!

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

capella07
Starting Member

46 Posts

Posted - 2007-02-28 : 14:18:21

The suggestions being posted that create a temp table are confusing me, I guess. Either I don't understand how they are being used, or I dont' understand how to implement those examples in what I'm doing, or I'm not explaining to you guys what I'm trying to accomplish well enough.

Those temp tables "hard code" the values (chicago, new york, etc, etc). I can't do that, 'cause if the data changes for a given field (Chicago is changed to Minneapolis, for example), then obviously the query would have to be rewritten.

The purpose of this query is to set up a stored procedure that an ASP.NET page will call to populate a table.

Thanks for all your help and I hope this clarifies where I'm coming from.

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 15:26:59

As I wrote in the suggestion, the first part is only for SETTING UP A TEST ENVIRONMENT!
The actual query is after the comment "Show the expected output".

The reason for doing this is that we can work with a limited set of samples. And it is faster to see and debug the query if it does not work the first time.

WHAT YOU HAVE TO DO, IS TO RENAME THE TABLE IN THE QUERY TO THE TABLE NAME USED IN YOUR ENVIRONMENT!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources