Solved

# String Builder

Posted on 2012-08-28
399 Views
I am trying to build a string based on the column values returned in a result set. Here is part of the result set that is returned.

pmap_no                   price       rank       Indicator
6081                     69.30      1      Y
6081            69.30      2      N
6081            60.30      3      N
6081            60.30      4      N
6081            60.30      5      N
6081            0.00      6      Y

The string I am trying to build comes from the price column and the indicator column. The string from this above result set would look like ’x-69.3-60.3-60.3-60.3-x

Here are the rules.

If indicator value = ‘Y’ then always ‘x’

If the price (money data type) is a whole number such as 12.00 then just the number to the left of the decimal. Such as ‘12’. A value of 0.00 is just ‘0’.

When looking to right of the decimal and greater than 0, follow this pattern.

12.10 = ’12.1’
12.02 = ’12.02’
12.15 = ’12.15’
12.70 = ’12.7’

Finally, between each rank throw in a ‘-‘ to separate the ranks.

Thanks
0
Question by:rwaterz

LVL 25

Accepted Solution

It looks ugly, but I could not figure out another way to format the number.  I custom function (UDF) would be useful (like Oracle's trim() which defaults to trimming spaces but can be overridden to trim other characters ... like '0')

``````;with test_data as (
select 6081 as pmap_no, 69.30 as price, 1 as rank, 'Y' as Indicator union all
select 6081 as pmap_no, 69.30 as price, 2 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 60.32 as price, 3 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 12.00 as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 60.03 as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 60.30 as price, 5 as rank, 'N' as Indicator union all
select 6081 as pmap_no,  0.00 as price, 6 as rank, 'Y' as Indicator
)
select distinct pmap_no
, stuff((select '-'+ case when Indicator = 'Y' then 'x'
else cast(floor(price) as varchar)
+ case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
+ case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end
end
from test_data v
where v.pmap_no = m.pmap_no
order by rank
for XML PATH('')), 1, 1, '') as string
from test_data m
``````
0

LVL 12

Expert Comment

Are your row numbers static? If so you could pivot the data set and then concatenate your string in the select statement

ex...
select isnull((convert(varchar,[1]),'X')+ '-'+isnull((convert(varchar,[2]),'X')+ etc...  as Column1
(
(SELECT price = case
when rank = 'Y' then null
else price
end, rank
FROM dataset) a
PIVOT (Sum(price) for rank in ('1','2','3','4','5','6')) as PVT

Otherwise  I don't see any other way to do it in SQL without using a cursor.
Good luck!
0

Author Comment

Your solution is about half way there. If I was to give it a price of 23.20 it returns 23.00.2.00

Also if the price was just 23.00, I need it to return 23 with no decimal or the zeros to the right of the decimal.

jared_s
The row numbers are not static. 6 would be in most cases but not a guarantee.

Thanks for the help!
0

LVL 25

Expert Comment

My results from the query posted as:
``````x-69.3-60.32-12-60.03-60.3-x
``````
The part removing the digits is
``````cast(floor(price) as varchar)
+ case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
+ case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end
``````
** technically it is stringing the number together by the bits when not 0
0

LVL 25

Expert Comment

What do you get when you run this SQL?
``````select price, cast(floor(price) as varchar)
+ case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
+ case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end as fmt_price
from (select 23.00 as price union all
select 15.43 union all
select 88.08 union all
select 12.3) v
``````

** late edit ... changed 23 to 23.00
0

Author Comment

I ran your original query as is and get the same results as you, but when I run it against my real data, I get the odd ball results. The only thing that may be the difference is that my price datatype is money. Maybe???
0

Author Comment

When I ran this...

select price, cast(floor(price) as varchar)
+ case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
+ case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end as fmt_price
from (select 23.00 as price union all
select 15.43 union all
select 88.08 union all
select 12.3) v

It seemed to come out good.

fmt_price
23
15.43
88.08
12.3
0

Author Closing Comment

I was able to get it to work with some slight modification.

case when PARSENAME(Price,2) > 0 then PARSENAME(Price,2) else '0'end +
case when PARSENAME(Price,1) > 0 then
Case When RIGHT(PARSENAME(Price,1),1) = 0 then '.' + Left(PARSENAME(Price,1),1) Else
'.' + PARSENAME(Price,1) end else ''end

Thanks for the help.
0

LVL 25

Expert Comment

Oh ... the money datatype.  Didn't think of that.  Glad you got it working.  I would have re-cast it as decimal first myself .. e.g.
``````;with test_data as (
select 6081 as pmap_no, cast(69.30 as money) as price, 1 as rank, 'Y' as Indicator union all
select 6081 as pmap_no, cast(69.30 as money) as price, 2 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(60.32 as money) as price, 3 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(12.00 as money) as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(60.03 as money) as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(60.30 as money) as price, 5 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast( 0.00 as money) as price, 6 as rank, 'Y' as Indicator
)
select distinct pmap_no
, stuff((select '-'+ case when Indicator = 'Y' then 'x'
else cast(floor(cast(price as decimal)) as varchar)
+ case when cast(price as decimal(12,2)) % 1 * 10 != 0 then '.' + cast(floor(cast(price as decimal(12,2)) % 1 * 10) as varchar) else '' end
+ case when cast(price as decimal(12,2)) % .1 * 100 != 0 then cast(floor(cast(price as decimal(12,2)) % .1 * 100) as varchar) else '' end
end
from test_data v
where v.pmap_no = m.pmap_no
order by rank
for XML PATH('')), 1, 1, '') as string
from test_data m
``````
0

## Featured Post

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…