Multiples address lines on a single row. Pivot?

  • 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.

  • 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

  • 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

  • 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

  • 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

  • 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]

  • 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.

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

  • Close. 20 rows with 5 address columns.

  • 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

  • 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!

  • 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

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

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