flatten out a normalized child table?

I need to extract Customer Order data, and join it to normalized ship-to
table so I can get their address on a single line/row of data. The Column
names are not importat in that final flat file, just what was in
row1,2,3,...6
GARY C Test Row1
LISA C Test Row2
816 RIVERVIEW PLACE Row3
WASHINGTON, MO 63090 Row4
Row5
Row6
THOMAS H Other-Test Row1
2102 N SHAMROCK RD Row2
BEL AIR, MD 21014 Row3
Row4 ,etc.
I have to account for double names names, and possibably titles, Suite #,
etc.
TIAPlease post DDL with your sample data. What is the key of the table you
posted? What relates the address lines together to make a single
address? Apparently nothing links an address together in the sketch you
posted except for the order in which you typed them out. We know that
tables have no fixed order so it isn't possible to combine the rows
reliably to make addresses out of each one.
If you had an additional column such as contact_name or contact_no for
each address line then you could do something like:
SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
MAX(CASE WHEN row_num = 2 THEN addr END),
MAX(CASE WHEN row_num = 3 THEN addr END),
... etc
FROM your_table
GROUP BY contact_name ;
Hope this helps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129058354.597302.39690@.g44g2000cwa.googlegroups.com...
> Please post DDL with your sample data. What is the key of the table you
> posted? What relates the address lines together to make a single
> address? Apparently nothing links an address together in the sketch you
> posted except for the order in which you typed them out. We know that
> tables have no fixed order so it isn't possible to combine the rows
> reliably to make addresses out of each one.
> If you had an additional column such as contact_name or contact_no for
> each address line then you could do something like:
> SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
> MAX(CASE WHEN row_num = 2 THEN addr END),
> MAX(CASE WHEN row_num = 3 THEN addr END),
> ... etc
> FROM your_table
> GROUP BY contact_name ;
Thanks