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 Monday, February 10, 2014 5:42 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
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!
Post #1539997
Posted Wednesday, February 12, 2014 3:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1540602
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse