Combine Multiple Rows into One 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)

Combine Multiple Rows into One Row

Author

Topic

eusanpe
Starting Member

5 Posts

Posted - 2007-11-12 : 22:17:02

Hello all:

I would like to know if the the following info below can be done.
I am using SQL Server 2000 and I can only perform lookups.

Here is an example of current table data.

Date Time Procedure Result
-------------- ------- --------- ------
12-Jul-2007 10:00 HGB 10.5
12-Jul-2007 10:00 HCT 31.0
13-Jul-2007 11:00 HGB 11.0
13-Jul-2007 11:00 HCT 33.0

What I would like it to look like is:

Date Time HGB HCT
----------- ----- ----- -----
12-Jul-2007 10:00 10.5 31.0
13-Jul-2007 11:00 11.0 33.0

Thanks,
Tony

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-12 : 22:30:57

[code]
Declare @t Table ([Date] Datetime, [Time] varchar(10), [Procedure] varchar(10), Result decimal(10,2))
Insert into @t
SELECT '12-Jul-2007', '10:00', 'HGB', 10.5 union all
SELECT'12-Jul-2007', '10:00', 'HCT', 31.0 union all
SELECT'13-Jul-2007', '11:00', 'HGB', 11.0 union all
SELECT'13-Jul-2007', '11:00', 'HCT', 33.0

SELECT distinct T.[Date], T.[Time], HGB.HGBResult as HGB, HCT.HCTResult As HCT
FROM @t T
JOIN( SELECT [Date] , [Time], Min(Result) HGBResult
FROM @T T1
WHERE [Procedure] = 'HGB'
GROUP BY [Date], [Time]
) AS HGB ON T.[Date] = HGB.[Date] AND T.[Time] = HGB.[Time]
JOIN ( SELECT [Date], [Time], Min(Result) HCTResult
FROM @T T2
WHERE [Procedure] = 'HCT'
GROUP BY [Date], [Time]
) AS HCT ON T.[Date] = HCT.[Date] AND T.[Time] = HCT.[Time]

[/code]

Fixed some logic error..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

eusanpe
Starting Member

5 Posts

Posted - 2007-11-12 : 22:38:52

dinakar:

Thank you very much.

Tony

eusanpe
Starting Member

5 Posts

Posted - 2007-11-12 : 23:09:50

One question:

Why would I run the following:

Declare @t Table ([Date] Datetime, [Time] varchar(10), [Procedure] varchar(10), Result decimal(10,2))
Insert into @t
SELECT '12-Jul-2007', '10:00', 'HGB', 10.5 union all
SELECT'12-Jul-2007', '10:00', 'HCT', 31.0 union all
SELECT'13-Jul-2007', '11:00', 'HGB', 11.0 union all
SELECT'13-Jul-2007', '11:00', 'HCT', 33.0

This data is just an example. I don't know what exactly is in the database. I have to run a query to get it out.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-13 : 11:58:19

I just created a table and put your data in to try the query. You dont need to run it..it also helps me in case you come back with other question I dont have to rewrite the INSERT scripts..I can just copy from my earlier post and continue working on it..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

eusanpe
Starting Member

5 Posts

Posted - 2007-11-13 : 14:14:43

I got it.. Thanks for your help.

I am working on my C# code along with this now.

Thanks again.
Tony

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 15:17:00

[code]-- Peso
SELECT [Date],
[Time],
MAX(CASE WHEN [Procedure] = 'HGB' THEN Result END) AS HGB,
MAX(CASE WHEN [Procedure] = 'HCT' THEN Result END) AS HCT
FROM @t
GROUP BY [Date],
[Time]
ORDER BY [Date],
[Time][/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-13 : 15:25:25

Peso's got it.

- Jeff
http://weblogs.sqlteam.com/JeffS

eusanpe
Starting Member

5 Posts

Posted - 2007-11-14 : 10:11:58

Peso:

Thank you..It works perfectly.

Tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources