Merging rows

Question

Post reply

Merging rows

Marlon Smith-Williams

Newbie

Points: 7
More actions
September 1, 2008 at 5:29 am

#192132

Hello,
I have a problem detailed below, that I need some help with.
Data for Person 1 is stored in Table 1 across multiple rows like this. (All values are character datatypes).
Person name age height
1 John NULL NULL
1 NULL 30 NULL
1 NULL NULL 192
I would like to use a SELECT statement to retrive the data on a single row.
Person name age height
1 John 30 192
I am sorry if this has been answered elsewhere I have seen similar questions posted but the examples given do not make much sense to me. Thanks in advance!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi There,

Try this:

DECLARE @Table TABLE

(Person VARCHAR(2),

name VARCHAR(10),

age VARCHAR(3),

height VARCHAR(3))

INSERT INTO @TABLE

SELECT '1', 'John', NULL, NULL UNION ALL

SELECT '1', NULL, '30', NULL UNION ALL

SELECT '1', NULL, NULL ,'192'

SELECT *

FROM @TABLE

SELECT

MAX(Person),

MIN(Name),

MIN(Age),

MIN(Height)

FROM @TABLE

GROUP BY PERSON

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

rosh-396756 SSCrazy Points: 2338 More actions · Answer 2

Is there any reason for storing data in this manner in the table?

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 3

rosh (9/1/2008)
Is there any reason for storing data in this manner in the table?

Heh... yep... someone doesn't have a good split function or didn't know how to do a crosstab on an EAV... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)