Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiples address lines on a single row. Pivot? Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 11:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
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.
Post #1538475
Posted Wednesday, February 5, 2014 11:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 6:58 AM
Points: 19, Visits: 77
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
Post #1538484
Posted Thursday, February 6, 2014 1:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,864, Visits: 14,165
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1538511
Posted Thursday, February 6, 2014 2:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:39 AM
Points: 470, Visits: 483
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
Post #1538522
Posted Thursday, February 6, 2014 2:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,864, Visits: 14,165
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1538526
Posted Thursday, February 6, 2014 5:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 2,873, Visits: 5,189

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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1538588
Posted Thursday, February 6, 2014 3:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
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.

Post #1538923
Posted Thursday, February 6, 2014 11:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 6:58 AM
Points: 19, Visits: 77
Do you mean that you could potentially have 20 rows of 3 address columns for one individual?
Post #1539013
Posted Saturday, February 8, 2014 6:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
Close. 20 rows with 5 address columns.
Post #1539543
Posted Monday, February 10, 2014 5:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
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
Post #1539988
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse