Multiples address lines on a single row. Pivot?

Question

Post reply

Multiples address lines on a single row. Pivot?

Danster

SSCommitted

Points: 1632
More actions
February 5, 2014 at 11:03 pm

#303781

I have scoured high & low for this solution, so in desperation I am posting here.
I have a table show multiple delivery addresses thus:
RowID, Address1, Address2, Address3, Address4,Postcode
ABC123, 13 This St, , , MySuburb, 21334
ABC123, 10 Another Rd, Building X, Smithville, 34563
ABC123, Unit 3, 10 Smith St, Jonesville, 4356
BCD987, 3 My Road, , , SuburbX, 234556
BCD987, 14 Long Rd, Unit 21, Badtown, 11223,
DJU765, 2 Short St, , , MySuburb, 21334
GTH453, 56 Treelined Ave, Niceplace, 2134
What I want is this:
RowID, 1stAddr1, 1stAddr2, 1stAddr3, 1stAddr4, 1stPostcode, 2ndAddr1, 2ndAddr2, 2ndAddr3 (etc)
ABC123, 13 This St,, , MySuburb, 21334, 10 Another Rd, Building X, Smithville, 34563, Unit 3, 10 Smith St, Jonesville, 4356
BCD987, 3 My Road, , , SuburbX, 234556, 14 Long Rd, Unit 21, Badtown, 11223
DJU765, 2 Short St, , ,MySuburb, 21334
GTH453, 56 Treelined Ave, NicePlace, 2134
Should I use STUFF somehow (I have a nice solution for just a single column & getting into a single row) or PIVOT? I am completely stumped.

Viewing 12 posts - 1 through 11 (of 11 total)

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

kyleiscoding SSC Rookie Points: 39 More actions · Answer 1

I would use a combination of Row_number() over(ID) and then pivot the data set on the Row_number and the users ID.

I hope this helps

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Edit: completely misunderstood the question. Where's the coffee?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLCJ SSCommitted Points: 1791 More actions · Answer 3

I could be completely wrong here but are you sure you do not want result set in one column ? And want it to spread across multiple columns ? If you want it in one column then PFB else you may want to try what ChrisM@Work has suggested ....

CREATE TABLE #TEMP

(

ROWID VARCHAR(100),

Address1 VARCHAR(100),

Address2 VARCHAR(100),

Address3 VARCHAR(100),

Address4 VARCHAR(100),

Postcode VARCHAR(100)

)

INSERT INTO #TEMP

SELECT 'ABC123', '13 This St','' ,'' , 'MySuburb', '21334' UNION ALL

SELECT'ABC123', '','10 Another Rd','Building X','Smithville','34563' UNION ALL

SELECT'ABC123', 'Unit 3', '','10 Smith St',' Jonesville',' 4356' UNION ALL

SELECT'BCD987', '3 My Road','' ,'' , 'SuburbX', '234556' UNION ALL

SELECT'BCD987', '14 Long Rd',' Unit 21','', 'Badtown', '11223' UNION ALL

SELECT 'DJU765', '2 Short St', '', '', 'MySuburb', '21334'

SELECT * FROM #TEMP

SELECT ROWID,STUFF((

SELECT ',' + Address1 + ',' + Address2 + ',' + Address3 + ',' + Address4 + ',' + Postcode FROM #TEMP INNER_TAB

WHERE INNER_TAB.ROWID = OUTER_TAB.ROWID

FOR XML PATH('')),1,1,'') FROM #TEMP OUTER_TAB

GROUP BY ROWID

DROP TABLE #TEMP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Danster (2/5/2014)
...
What I want is this:
RowID, 1stAddr1, 1stAddr2, 1stAddr3, 1stAddr4, 1stPostcode, 2ndAddr1, 2ndAddr2, 2ndAddr3 (etc)
...

What's the maximum number of addresses per RowID?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

What I want is this:
RowID, 1stAddr1, 1stAddr2, 1stAddr3, 1stAddr4, 1stPostcode, 2ndAddr1, 2ndAddr2, 2ndAddr3 (etc)

If you need just two columns: RowId and ConcatenatedAddress - use advised FOR XML with STUFF thingy.

If you really want what you have asked - pivotted multiple address columns - you need dynamic cross-tab. Here the one from J.Moden to help:

http://www.sqlservercentral.com/articles/Crosstab/65048/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Danster SSCommitted Points: 1632 More actions · Answer 6

Cheers all for those answers. I'll give them a go later today. For Chris, max no of rows is unlimited but in actual reality, I guess there wouldn't be more than 20 delivery addresses for each ID.

kyleiscoding SSC Rookie Points: 39 More actions · Answer 7

Do you mean that you could potentially have 20 rows of 3 address columns for one individual?

Danster SSCommitted Points: 1632 More actions · Answer 8

Danster

SSCommitted

Points: 1632

February 8, 2014 at 6:40 pm

#1687352

Close. 20 rows with 5 address columns.

Danster SSCommitted Points: 1632 More actions · Answer 9

Correction to the required solution:

Using the sample tables provided in original post, the solution is to get only up to 4 addresses, even if each ROWID has more.

Also, the result will be 5 columns thus:

ROWID, Address1, Address2, Address3, Address4

Each address will be a concatenated address (Address1 + Address2 + Address3 + ADdress4 + Postcode)

Still stumped on how to achieve this, so any help is appreciated.

Cheers, Danster

Danster SSCommitted Points: 1632 More actions · Answer 10

Update...Couldn't wait as this was an urgent request! (Aren't they all).

1st step. Created & saved a view with columns mentioned above, plus an extra LineNumber column.

View is this

SELECT RowID, Address1 + ' ' + Address 2 + ' ' (etc) as DelAddr, row_number() OVER (partition BY RowID

ORDER BY RowID) AS LineNumber

FROM MyTable

2nd Step.

Save another view like this:

SELECT RowID,

MAX(CASE WHEN LineNumber = 1 THEN DelAddr ELSE '' END) as DelAddress1,

MAX(CASE WHEN LineNumber = 2 THEN DelAddr ELSE '' END) as DelAddress2,

MAX(CASE WHEN LineNumber = 3 THEN DelAddr ELSE '' END) as DelAddress3,

MAX(CASE WHEN LineNumber = 4 THEN DelAddr ELSE '' END) as DelAddress4

FROM MyView1

GROUP BY RowID

SWEEEET!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Nice work. I'd miss out the first view though - unless it's going to be used elsewhere, it's simply clutter:

SELECT RowID,

MAX(CASE WHEN LineNumber = 1 THEN DelAddr ELSE '' END) as DelAddress1,

MAX(CASE WHEN LineNumber = 2 THEN DelAddr ELSE '' END) as DelAddress2,

MAX(CASE WHEN LineNumber = 3 THEN DelAddr ELSE '' END) as DelAddress3,

MAX(CASE WHEN LineNumber = 4 THEN DelAddr ELSE '' END) as DelAddress4

FROM (

SELECT

RowID,

Address1 + ' ' + Address2 + ' ' (etc) as DelAddr,

row_number() OVER (partition BY RowID ORDER BY RowID) AS LineNumber

FROM MyTable

) d

GROUP BY RowID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden