adding spaces to a to information

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding spaces to a to information

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-10-25 : 09:07:50

this is what i'm trying to do

current result

load ship xpd xpp xpl srt jjj mmm reason
111 1 1 1 1 0 0 0 null
118 4 0 0 1 0 0 1 null

desired result

load ship xpd xpp xpl srt jjj mmm reason
111 1 1 1 1 0 0 0 ship xpd xpp xpl
118 4 0 0 1 0 0 1 xpl mmm

here's the code I have

UPDATE [shipments]
SET [reason] = RTRIM ('xpd') +' ' + SPACE(2) + LTRIM ('xpp')
FROM [shipments]
WHERE xpd = '1'
AND xpp = '1'

I have multiple columns that i need to have update reason (over 20).
How would i change the code I have to handle this. When the column has a 1 it's valid and i want the title of that column to appear in the reason column if it's 0 it's invalid and i don't need to do anything

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-25 : 09:36:18

[code]
UPDATE Yourtable SET Reason =
CASE WHEN [Load] = 1 then 'Load' + SPACE(2) else '' END +
CASE WHEN ship = 1 then 'ship' + SPACE(2) else '' END +
CASE WHEN xpd = 1 then 'xpd' + SPACE(2) else '' END +
CASE WHEN xpp = 1 then 'xpp' + SPACE(2) else '' END +
CASE WHEN xpl = 1 then 'xpl' + SPACE(2) else '' END +
CASE WHEN srt = 1 then 'srt' + SPACE(2) else '' END +
CASE WHEN jjj = 1 then 'jjj' + SPACE(2) else '' END +
CASE WHEN mmm = 1 then 'mmm' + SPACE(2) else '' END
[/code]

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER