0

I am a novice in SQL world; I've been searching the site for couple of hours and could not find an answer.

I'm using SQL Server Management Studio to arrange a table of my customers with columns: 

name, address1, address2, address3, city, state, zipcode, 
item_purchased, quantity_purchased, etc...

However what I've been trying to do is to format this table as texts in such a way:

John Smith
123 Sql Street
Los Angeles, CA 90001

item_purchased - quantity_purchased

Would it be possible to achieve this in SQL? If not, would be very interested to know a way around in Java or Python input/output method.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
brad
  • 1
  • 1
    Possible duplicate of [How to insert a line break in a SQL Server VARCHAR/NVARCHAR string](http://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string) – Kamil Gosciminski Sep 22 '16 at 15:20
  • Thank you for your replies! I've been following the link provided; trying the print. How do I assign the value of row 1 of column 1 to the print variable? – brad Sep 22 '16 at 15:48
  • Possible duplicate of [SQL to XML to XSLT to HTML, or SQL to HTML?](http://stackoverflow.com/questions/4870478/sql-to-xml-to-xslt-to-html-or-sql-to-html) – Paul Sweatte Sep 22 '16 at 15:49
  • you can use SELECT name + CHAR(13) + address1 + CHAR(13) + address2 + CHAR(13) + address3 + CHAR(13) + city + CHAR(13) + state + CHAR(13) + zipcode in it's simplest form, but this doesn't take into account where address2 or address3 might be empty, thus producing empty lines. – LogicalMan Sep 22 '16 at 16:04
  • The following line can be substituted for any column in the address you believe could be empty : CASE WHEN LTRIM(RTRIM(ISNULL([address2],''))) <> '' THEN LTRIM(RTRIM(ISNULL([address2],''))) + CHAR(13) ELSE '' END – LogicalMan Sep 22 '16 at 16:11

1 Answers1

0

Brad,

Try this:

SELECT name + CHAR(13)
+ CASE WHEN LTRIM(RTRIM(ISNULL([address1],''))) <> '' THEN LTRIM(RTRIM(ISNULL([address1],''))) + CHAR(13) ELSE '' END
+ CASE WHEN LTRIM(RTRIM(ISNULL([address2],''))) <> '' THEN LTRIM(RTRIM(ISNULL([address2],''))) + CHAR(13) ELSE '' END
+ CASE WHEN LTRIM(RTRIM(ISNULL([address3],''))) <> '' THEN LTRIM(RTRIM(ISNULL([address3],''))) + CHAR(13) ELSE '' END
+ CASE WHEN LTRIM(RTRIM(ISNULL([city],''))) <> '' THEN LTRIM(RTRIM(ISNULL([city],''))) + CHAR(13) ELSE '' END
+ CASE WHEN LTRIM(RTRIM(ISNULL([state],''))) <> '' THEN LTRIM(RTRIM(ISNULL([state],''))) + CHAR(13) ELSE '' END
+ CASE WHEN LTRIM(RTRIM(ISNULL([zipcode],''))) <> '' THEN LTRIM(RTRIM(ISNULL([zipcode],''))) + CHAR(13) ELSE '' END
+ CASE WHEN LTRIM(RTRIM(ISNULL([item_purchased],''))) <> '' THEN LTRIM(RTRIM(ISNULL([item_purchased],''))) ELSE '' END
+ ' - ' + CASE WHEN LTRIM(RTRIM(ISNULL([quantity_purchased],''))) <> '' THEN LTRIM(RTRIM(ISNULL([quantity_purchased],''))) + CHAR(13) ELSE '' END
FROM Customers

You can either assign the output to a variable for a single row or into another table for printing.

LogicalMan
  • 384
  • 2
  • 6
  • Thanks! LogicalMan, that is a great tip. I have been struggling because I am not too familiar with SQL keywords. Thanks again! – brad Sep 23 '16 at 00:57