Combing row results into a new column?

Question

Combing row results into a new column?

NadJ

Ten Centuries

Points: 1031
More actions
December 5, 2004 at 11:13 am

#61378

Can anyone show me how I can go about displaying my results so that I only have one row for each airport name, and all the values of R1 and R2 for that airport appended one by one to a new column.
Here is the query:
SELECT ARPT.NAME, ARPT.ICAO, RWY.R1, RWY.R2, ARPT.ID, RWY.ID
FROM ARPT, RWY WHERE ARPT.ID=RWY.ID AND ICAO like 'K%' ORDER BY ARPT.ICAO
Name:                            ICAO        R1      R2           ID            ID
LEHIGH VALLEY INTL          KABE        31     13      US09256         US09256
LEHIGH VALLEY INTL          KABE        24     06      US09256         US09256
ABILENE RGNL                   KABI        35R    17L     US65504        US65504
ABILENE RGNL                   KABI        22      04      US65504        US65504
ABILENE RGNL                   KABI        35L     17R    US65504        US65504
ABERDEEN RGNL                KABR        35      17     US15077         US15077
ABERDEEN RGNL                KABR        31      13      US15077        US15077
SOUTHWEST GEORGIA       KABY        34      16      US78898        US78898
SOUTHWEST GEORGIA       KABY        22      04      US78898        US78898
NANTUCKET MEM              KACK        30      12     US18941        US18941
NANTUCKET MEM              KACK        33      15     US18941       US18941
NANTUCKET MEM              KACK        24      06      US18941      US18941
Basically, I'm looking for something like this:
LEHIGH VALLEY INTL          KABE        31, 13, 24, 06...
ABILENE RGNL                   KABI        35R , 17L, 22, 04, 35L, 17R...
ABERDEEN RGNL                KABR        35, 17, 31, 13...
SOUTHWEST GEORGIA       KABY        34. 16, 22, 04 ...
NANTUCKET MEM              KACK        30, 12, 33, 15, 24, 06...
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

The standard answer to this type of question is that this is something best done in the client, not at the SQL Server level.

But in cases where you can't (or don't want) to do this, there are methods to aggregate rows into comma-delimited lists. Beware that they will not perform especially well for large numbers of rows. Try it in your case and see how it works.

First, create a user defined function to return a list of R1 and R2. This does presume that any given list is not greater than 8000 characters.

CREATE FUNCTION dbo.fn_R_List
   (@ID   char(7)
   )
RETURNS varchar(8000)
AS
BEGIN
   declare @R_List varchar(8000)
   SELECT @R_List = COALESCE(@R_List + ', ', '') + R1 + ', ' + R2
   FROM    RWY
   WHERE   ID = @ID
   RETURN @R_List
END

Then use this function in your query:

SELECT ARPT.NAME, ARPT.ICAO, dbo.fn_R_List(ARPT.ID)
FROM ARPT
WHERE ICAO like 'K%'
ORDER BY ARPT.ICAO

Scott Thornburg

Volt Information Sciences

NadJ Ten Centuries Points: 1031 More actions · Answer 2

Miracle Worker!

Absolutely amazing. It works exactly as I want it to. I knew I was wasting my time with the articles at http://www.sqlteam.com (a blatant overkill).

I honestly can't thank you enough.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

To be fair, SQLTeam is also an interesting site. A comparison between the method suggested by Scott and another one can be found here on the blog of SQL Server MVP Adam Machanic:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NadJ Ten Centuries Points: 1031 More actions · Answer 4

Hi again,

I'm trying to adapt the above to be a bit more generic, i.e. for use in a few scenarios I have. Here it is:

CREATE FUNCTION dbo.fn_Built_List (

@ARPT_IDENT char(7), @Table_Name char (25),

@Column1 char(25), @Column2 char(25), @Column3 char(25),

@Column4 char(25), @Column5 char(25), @Column6 char(25),

@Column7 char(25), @column8 char(25), @Column9 char(25)

)

RETURNS varchar(8000)

AS

BEGIN

declare @Built_List varchar(8000)

SELECT @Built_List = COALESCE(@Built_List + ', ', '')

+ @Column1 + ', ' + @Column2 + ', ' + @Column3

+ ', ' + @Column4 + ', ' + @Column5 + ', ' + @Column6

+ ', ' + @Column7 + ', ' + @column8 + ', ' + @Column9

FROM @Table_Name

WHERE ARPT_IDENT = @ARPT_IDENT

RETURN @Built_List

END

This returns:

Server: Msg 137, Level 15, State 2, Procedure fn_Built_List, Line 17

Must declare the variable '@Table_Name'. Am I declaring the table variable in the right place?

Thanks again.

Yoda-193403 SSC Eights! Points: 988 More actions · Answer 5

One UDF per summary, write you must. Dynamic SQL, a UDF cannot use. Nor can you, in the manner in which you tried. SELECT * FROM @TableName, requires a local table variable, it does.

Presentation layer, much easier to do.