Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Multiples address lines on a single row. Pivot? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 10, 2014 5:42 PM
 Valued Member Group: General Forum Members Last Login: Monday, August 1, 2016 7:22 PM Points: 52, Visits: 90
 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 thisSELECT RowID, Address1 + ' ' + Address 2 + ' ' (etc) as DelAddr, row_number() OVER (partition BY RowIDORDER BY RowID) AS LineNumberFROM MyTable2nd 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 DelAddress4FROM MyView1GROUP BY RowIDSWEEEET!
Post #1539997
 Posted Wednesday, February 12, 2014 3:19 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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 DelAddress4FROM ( SELECT RowID, Address1 + ' ' + Address2 + ' ' (etc) as DelAddr, row_number() OVER (partition BY RowID ORDER BY RowID) AS LineNumber FROM MyTable) dGROUP BY RowID` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1540602

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.