DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to generate address labels?

Execute the following Microsoft SQL Server T-SQL script in Management Studio Query Editor to demonstrate the generation of address labels.

Set Query Results to Text when executing script in Management Studio. CHAR(13) is Carriage Return (CR).

USE AdventureWorks;

GO

SET NOCOUNT ON

GO

SELECT AddressLabel =

c.FirstName + ' ' + c.LastName+char(13) +

a.AddressLine1 + char(13) +

CASE WHEN AddressLine2 is NOT NULL THEN AddressLine2+char(13)

     ELSE '' END +

a.City+', '+StateProvinceCode+ ' ' + a.PostalCode +char(13)

FROM Person.Contact AS c

    INNER JOIN HumanResources.Employee AS e

        ON c.ContactID = e.ContactID

    INNER JOIN HumanResources.EmployeeAddress ea

        ON ea.EmployeeID = e.EmployeeID

    INNER JOIN Person.[Address] AS a

        ON a.AddressID = ea.AddressID

    INNER JOIN Person.StateProvince sp

        ON sp.StateProvinceID = a.StateProvinceID

ORDER BY LastName, FirstName;

GO
/*
AddressLabel
--------------------------------------------------------
Syed Abbas
7484 Roundtree Drive
Bothell, WA  98011
 
Kim Abercrombie
9752 Jeanne Circle
Carnation, WA  98014
 
Hazem Abolrous
5050 Mt. Wilson Way
Kenmore, WA  98028
 
Pilar Ackerman
5407 Cougar Way
Seattle, WA  98104
....

*/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE